Create or replace trigger before insert on field in sql

1.Create  or replace trigger before insert on field in sql

 Create or replace trigger t1 Before insert on Employee  
 For each row  
 declare  
 v_firstname Employee.firstname%type;  
 v_lastname Employee.lastname%type;  
 Begin  
 DBMS_OUTPUT.PUT_LINE('You inserted the first Name:'|| :NEW.FIRSTNAME);  
 DBMS_OUTPUT.PUT_LINE('You inserted the first Name:'|| :NEW.LASTNAME);  
 Begin  
 select distinct firstname into v_firstname from Employee where firstname=:NEW.FIRSTNAME;  
 IF(:NEW.FIRSTNAME = v_firstname) THEN  
 DBMS_OUTPUT.PUT_LINE('This first name exists already in the table Employee.');  
 end if;  
 exception  
 when no_data_found  
 then  
 DBMS_OUTPUT.PUT_LINE('This first name does not exist in the table Employee.');  
 End;  
 Begin  
 select distinct lastname into v_lastname from Employee where lastname=:NEW.lastNAME;  
 IF(:NEW.LASTNAME = v_lastname) THEN  
 DBMS_OUTPUT.PUT_LINE('This last name exists already in the table Employee.');  
 end if;  
 exception  
 when no_data_found  
 then  
 DBMS_OUTPUT.PUT_LINE('This last name does not exist in the table Employee.');  
 End;  
 Begin  
 IF(:NEW.FIRSTNAME = v_firstname AND :NEW.LASTNAME = v_lastname)  
 THEN  
 DBMS_OUTPUT.PUT_LINE('This combination of first name and last name exists already in the table Employee.');  
 ELSE  
 DBMS_OUTPUT.PUT_LINE('This combination of first name and last name does not exist in the table Employee.');  
 END IF;  
 End;  
 End;  

2.Example of INSERT trigger to fire upon the insert of a new record

Summary:

This is the Example of INSERT trigger to fire upon the insert of a new record and referencing table within mail. I hope you able get some idea of trigger by this example.

Details:

 CREATE TRIGGER NewTestTrigger1 ON [DBO].[t_tasks_master_tasks] AFTER INSERT AS  
 SELECT a.Task_ID, a.Name, b.TEST  
 FROM t_tasks_master_tasks a, t_tasks_lu_TEST b  
 WHERE a.TEST_ID = b.TEST_ID  
 DECLARE @Name nvarchar (100)  
 DECLARE @TEST nvarchar (10)  
 SET @Name = (SELECT Name FROM inserted)  
 SET @TEST = ([t_tasks_lu_TEST].[TEST])  
 BEGIN  
 DECLARE @msg varchar(500)  
 SET @msg = 'New task created: ' + @Name + ' For TEST: ' + @TEST  
 EXEC msdb.dbo.sp_send_dbmail @xyz@gmail.com ', @body= @msg, @subject = 'New test entered', @profile_name = 'xyzprofile'  
 END  

3. Update status of Employee using trigger in Procedural language in sql

To Update status of Employee using trigger in Procedural language in sql you can take help of following example.

 create or replace trigger emp_stat_updatetrig  
  before insert or update  
  on emp  
  for each row  
  declare  
  v_sal number;  
  begin  
   v_sal := :new.sal;  
   if v_sal < 50 then  
     :new.status := 'A';  
   elsif v_sal = 50 then  
      :new.status := 'B';  
   else  
      :new.status := 'C';  
   end if;  
  end;  

4.Code to Create trigger for insert

 CREATE TRIGGER mytrig  
 ON s1.sh  
 FOR INSERT  
 AS  
 DECLARE @ModifiedDate datetime  
 SELECT @ModifiedDate=ModifiedDate FROM Inserted  
 IF (@ModifiedDate!=getdate())  
 BEGIN  
 PRINT 'HI'  
 END  
 RETURN