TRIGGER Examples

TRIGGERS
SYNTAX: CREATE [OR REPLACE] TRIGGER triggername             {BEFORE | AFTER}             {DELETE, INSERT, UPDATE [OF columnname….]}             ON tablename             [REFERENCING {OLD AS old, NEW AS new}]             [FOR EACH ROW [WHEN condition]]             DECLARE                         Variable declaration;                         Constant declaration;             BEGIN                         PL/SQL subprogram body;             [EXCEPTION]                         [exception PL/SQL block;]             END;
Data Dictionary for TRIGGER:  USER_TRIGGERS   SQL> desc USER_TRIGGERS   Name                                      Null?    Type —————————————– ——– —————–   TRIGGER_NAME                                       VARCHAR2(30) TRIGGER_TYPE                                       VARCHAR2(16) TRIGGERING_EVENT                                   VARCHAR2(227) TABLE_OWNER                                        VARCHAR2(30) BASE_OBJECT_TYPE                                   VARCHAR2(16) TABLE_NAME                                         VARCHAR2(30) COLUMN_NAME                                        VARCHAR2(4000) REFERENCING_NAMES                                  VARCHAR2(128) WHEN_CLAUSE                                        VARCHAR2(4000) STATUS                                             VARCHAR2(8) DESCRIPTION                                        VARCHAR2(4000) ACTION_TYPE                                        VARCHAR2(11) TRIGGER_BODY                                       LONG   Eg: SQL> select trigger_name,triggering_event,trigger_body from user_triggers where table_name=’EMP_TEMP’;
	We can disable / enable the trigger by the following syntax:   ALTER TRIGGER <trigger name> DISABLE / ENABLE;   We can drop the trigger by the following syntax: DROP TRIGGER trigger_name;

 

 

1	— First Trigger Hello CREATE OR REPLACE TRIGGER hello BEFORE INSERT ON emp_temp FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(‘hello! I am trigger…I will execute every time whenever u will insert new record in table emp’); END; /
2	create or replace trigger t_temp before update on stud for each row declare begin insert into t_stud values(:old.rollno,:old.nm); dbms_output.put_line(‘old data stored in backup table’); end; /   update stud set nm=’abc’ where rollno=11;
3	CREATE OR REPLACE TRIGGER minSalary BEFORE INSERT ON emp FOR EACH ROW BEGIN IF (:new.sal < 1000) THEN RAISE_APPLICATION_ERROR (-20004, ‘Minimum Salary should be 1000’); END IF; END; /   CREATE OR REPLACE TRIGGER minSalary BEFORE INSERT ON emp FOR EACH ROW WHEN (new.sal < 1000) BEGIN RAISE_APPLICATION_ERROR (-20004, ‘Minimum Salary should be 1000’); END; /
4	/* WAT which check and verify whether name starts with ‘S’ and length of name is greater than 4 when user insert record in emp table. If condition not satisfied raise the error.   CREATE OR REPLACE TRIGGER check BEFORE INSERT OR UPDATE OF name ON emp FOR EACH ROW WHEN (UPPER(NEW.NAME) NOT LIKE ‘S%’ AND (LENGTH(NEW.NAME) <=4)) BEGIN RAISE_APPLICATION_ERROR(-20001,’name must start with S and minimum 5 character long’); END; /
5	create or replace trigger t_old before insert on emp_temp for each row begin if substr(:new.empno,1,1)!=’E’ then :new.empno:=lpad(:new.empno,5,0); :new.empno:=’E’||(:new.empno); end if; end; /
6	–STATEMENT TRIGGER create or replace trigger t_stud after insert or update or delete on emp_temp begin if INSERTING then dbms_output.put_line(‘ inserted’); elsif UPDATING then dbms_output.put_line(‘ updated’); elsif DELETING then dbms_output.put_line(‘ deleted’); end if; end; SQL> update emp_temp set eno=9 where enm=’Swara’;
7	–this trigger will calculate the area of circle and insert it in the — table when user will insert radius in the table.   create or replace trigger syn_emp instead of insert on vw_emp for each row   declare   begin             dbms_output.put_line(‘Hello! I am instead of Trigger ‘); end;     SQL> delete from vw_emp where empno=7902;
8	–this trigger will calculate the area of circle and insert it in the — table when user will insert radius in the table. create or replace trigger t_area before insert on areas for each row   declare x number; y number; begin :new.area := (3.14* :new.radius* :new.radius); end;
9	/*–WAT which display the error message if user enters the qnty ordered less than 0 in sales_order table */   create or replace trigger t_sales_order before insert on sales_order for each row when new.qty_ordered<0 begin raise_application_error(-20000,’qty ordered must be greater than 0′); end; /
10	/*  WAT to generate primary key using sequence and insert its values in rollno of stud table */ /*       CREATE SEQUENCE seq1 start with 1 increment by 1;   */ CREATE OR REPLACE TRIGGER T_PrimaryKey BEFORE INSERT on student   FOR EACH ROW   DECLARE id NUMBER(4); BEGIN SELECT seq1.NEXTVAL INTO id FROM DUAL; :NEW.rollno := id; END; /
11	CREATE OR REPLACE TRIGGER Working_hrs BEFORE INSERT OR UPDATE OR DELETE ON emp DECLARE WORKING_HOURS NUMBER(2); BEGIN WORKING_HOURS := TO_CHAR(SYSDATE,’HH24′); IF WORKING_HOURS < 10 AND WORKING_HOURS > 18 THEN RAISE_APPLICATION_ERROR(-20001,’Its not official working hours! Can not do operations on emp’); END IF; END; /
12	CREATE OR REPLACE TRIGGER Weekend BEFORE INSERT OR UPDATE OR DELETE ON emp_temp for each row WHEN TRIM((TO_CHAR(SYSDATE,’day’)) IN((‘SATURDAY’,’SUNDAY’))) DECLARE BEGIN RAISE_APPLICATION_ERROR(-20001,’Enjoy your Weekend!! Do not do work!!’ ); END;
13.	/* WAT which keeps backup of data of emp table for each update and delete operation in table EMP_backup.  Along with employee no and name also store operation date and operation type (UPDATE or DELETE) in the backup table. EMP_Backup(eno, enm, oper_date, oper_type)   */   CREATE OR REPLACE TRIGGER audit_trail BEFORE UPDATE OR DELETE on emp FOR EACH ROW DECLARE   BEGIN if UPDATING then INSERT INTO emp_backup values(:old.eno, :old.enm, sysdate, ‘UPDATE’); elsif DELETING then INSERT INTO emp_backup values(:old.eno, :old.enm, sysdate, ‘DELETE’); end if; end; END;

 

 

 

Tagged: ALTER TRIGGER, Data Dictionary for TRIGGER, DELETING trigger, disable / enable trigger, DROP TRIGGER, example of STATEMENT TRIGGER, Examples of triggers, how to create trigger in oracle, how to create trigger?, sunday trigger, syntax to disable / enable the trigger, TRIGGER, TRIGGER Examples, trigger of audit trail, trigger to generate primary key, trigger to generate primary key using sequence, trigger to implement check constraint, trigger to implement constraint, trigger to to maintain the security, trigger which raise the error, UPDATING, USER_TRIGGERS, using INSERTING, using INSERTING in trigger, weekend check trigger