SQL> --trigger SQL> clear screen SQL> drop trigger t1 2 ; Trigger dropped. SQL> drop trigger t2; drop trigger t2 * ERROR at line 1: ORA-04080: trigger 'T2' does not exist SQL> truncate bff; truncate bff * ERROR at line 1: ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword SQL> truncate table bff; Table truncated. SQL> desc bff Name Null? Type ----------------------------------------- -------- ---------------------------- NAME VARCHAR2(10) AGE NUMBER SQL> create or replace trigger t1 2 before insert on bff 3 for each row 4 begin 5 . SQL> ed Wrote file afiedt.buf 1 create or replace trigger t1 2 before insert or update or delete on bff 3 for each row 4 begin 5 dbms_output.put_line('row level before trigger'); 6 dbms_output.put_line('user='||user||' date='||sysdate); 7 dbms_output.put_line('new name='||:new.name||'old name='||:old.name); 8* end; 9 / Trigger created. SQL> insert into bff 2 values('ayse',66); row level before trigger user=USER15 date=16-MAY-17 new name=ayseold name= 1 row created. SQL> update bff 2 . SQL> insert into bff 2 values('ali',55); row level before trigger user=USER15 date=16-MAY-17 new name=aliold name= 1 row created. SQL> select * from bff; NAME AGE ---------- ---------- ayse 66 ali 55 SQL> update 2 SQL> update bff 2 set name=upper(name); row level before trigger user=USER15 date=16-MAY-17 new name=AYSEold name=ayse row level before trigger user=USER15 date=16-MAY-17 new name=ALIold name=ali 2 rows updated. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t2 2 before insert or delete or update on bff 3 declare 4 begin 5 dbms_output.put_line('statement level before trigger'); 6* end; SQL> / Trigger created. SQL> update bff 2 set name=upper(name); statement level before trigger row level before trigger user=USER15 date=16-MAY-17 new name=AYSEold name=AYSE row level before trigger user=USER15 date=16-MAY-17 new name=ALIold name=ALI 2 rows updated. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t2 2 before insert or delete or update on bff 3 declare 4 begin 5 dbms_output.put_line('ST:statement level before trigger'); 6 dbms_output.put_line('ST:the user is'||user); 7 dbms_output.put_line('ST:the sysdate is'||sysdate); 8* end; SQL> / Trigger created. SQL> update bff 2 set name=upper(name); ST:statement level before trigger ST:the user isUSER15 ST:the sysdate is16-MAY-17 row level before trigger user=USER15 date=16-MAY-17 new name=AYSEold name=AYSE row level before trigger user=USER15 date=16-MAY-17 new name=ALIold name=ALI 2 rows updated. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t2 2 before insert or delete or update on bff 3 declare 4 begin 5 dbms_output.put_line('ST:statement level before trigger'); 6 dbms_output.put_line('ST:the user is'||user); 7 dbms_output.put_line('ST:the sysdate is'||sysdate); 8 dbms_output.put_line('ST:old name'||:old.name); 9* end; 10 / create or replace trigger t2 * ERROR at line 1: ORA-04082: NEW or OLD references not allowed in table level triggers SQL> drop trigger t2; Trigger dropped. SQL> create table bff_audit( 2 username varchar2(10), 3 mod_date date, 4 oper varchar2(7) , 5 prev_name varchar2(10), 6 new_name varchar2(10), 7 prev_age number 8 new_age number); new_age number) * ERROR at line 8: ORA-00907: missing right parenthesis SQL> 7 prev_age number, SQL> l 1 create table bff_audit( 2 username varchar2(10), 3 mod_date date, 4 oper varchar2(7) , 5 prev_name varchar2(10), 6 new_name varchar2(10), 7 prev_age number, 8* new_age number) SQL> / Table created. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t1 2 before insert or update or delete on bff 3 for each row 4 begin 5 insert into bff_audit(username,mod_date,oper,prev_name,new_name,prev_age,new_age) 6 values(user,sysdate,null,:old.name,:new.name,:old.age,:new.age); 7* end; SQL> / Trigger created. SQL> select * from bff; NAME AGE ---------- ---------- AYSE 66 ALI 55 SQL> select * from bff_audit; no rows selected SQL> grant all on bff to public; Grant succeeded. SQL> insert into bff 2 values('nazife',27); 1 row created. SQL> select * from bff; NAME AGE ---------- ---------- AYSE 66 ALI 55 nazife 27 SQL> select * from bff_audit; USERNAME MOD_DATE OPER PREV_NAME NEW_NAME PREV_AGE NEW_AGE ---------- --------- ------- ---------- ---------- ---------- ---------- USER15 16-MAY-17 nazife 27 SQL> SQL> select * from bff; NAME AGE ---------- ---------- AYSE 66 ALI 55 nazife 27 SQL> commit; Commit complete. SQL> select * from bff; NAME AGE ---------- ---------- adriana 33 hasan 24 AYSE 66 ALI 55 nazife 27 Ahmed 20 ugur 21 Ahmed 20 8 rows selected. SQL> / NAME AGE ---------- ---------- adriana 33 hasan 24 AYSE 66 ALI 55 nazife 27 Ahmed 20 ugur 21 Ahmed 20 8 rows selected. SQL> / NAME AGE ---------- ---------- adriana 33 hasan 24 AYSE 66 ALI 55 nazife 27 Ahmed 20 ugur 21 Ahmed 20 8 rows selected. SQL> / NAME AGE ---------- ---------- molhim 22 adriana 33 hasan 24 AYSE 66 ALI 55 nazife 27 Ahmed 20 ugur 21 Ahmed 20 9 rows selected. SQL> / NAME AGE ---------- ---------- molhim 22 ibraheem 19 adriana 33 hasan 24 AYSE 66 ALI 55 nazife 27 Ahmed 20 ugur 21 Ahmed 20 10 rows selected. SQL> select * from bff_audit; USERNAME MOD_DATE OPER PREV_NAME NEW_NAME PREV_AGE NEW_AGE ---------- --------- ------- ---------- ---------- ---------- ---------- USER17 16-MAY-17 molhim 22 USER10 16-MAY-17 ibraheem 19 USER1 16-MAY-17 adriana 33 USER12 16-MAY-17 hasan 24 USER15 16-MAY-17 nazife 27 USER14 16-MAY-17 Ahmed 20 USER16 16-MAY-17 ugur 21 USER14 16-MAY-17 Ahmed 20 8 rows selected. SQL> --write a trigger that will ensure that all names entered are in upper case and that the age is between 10 and 60 SQL> select * from bff; NAME AGE ---------- ---------- molhim 22 ibraheem 19 adriana 33 hasan 24 AYSE 66 ALI 55 nazife 27 Ahmed 20 ugur 21 Ahmed 20 10 rows selected. SQL> --if age is not between 10 and 60 assign 25 SQL> ed Wrote file afiedt.buf 1 create or replace trigger t3 2 before insert or update on bff 3 for each row 4 begin 5 if :new.age not between 10 and 60 then 6 :new.age :=25; 7 end if; 8 :new.name := upper(:new.name); 9* end; SQL> / Trigger created. SQL> select * from bff; NAME AGE ---------- ---------- molhim 22 ibraheem 19 adriana 33 hasan 24 AYSE 66 ALI 55 nazife 27 Ahmed 20 ugur 21 Ahmed 20 10 rows selected. SQL> select * from bff_audit; USERNAME MOD_DATE OPER PREV_NAME NEW_NAME PREV_AGE NEW_AGE ---------- --------- ------- ---------- ---------- ---------- ---------- USER17 16-MAY-17 molhim 22 USER10 16-MAY-17 ibraheem 19 USER1 16-MAY-17 adriana 33 USER12 16-MAY-17 hasan 24 USER15 16-MAY-17 nazife 27 USER14 16-MAY-17 Ahmed 20 USER16 16-MAY-17 ugur 21 USER14 16-MAY-17 Ahmed 20 8 rows selected. SQL> insert into bff 2 values('veli',9); 1 row created. SQL> select * from bff; NAME AGE ---------- ---------- molhim 22 ibraheem 19 adriana 33 hasan 24 AYSE 66 ALI 55 nazife 27 Ahmed 20 ugur 21 Ahmed 20 VELI 25 11 rows selected. SQL> select * from bff_audit; USERNAME MOD_DATE OPER PREV_NAME NEW_NAME PREV_AGE NEW_AGE ---------- --------- ------- ---------- ---------- ---------- ---------- USER17 16-MAY-17 molhim 22 USER10 16-MAY-17 ibraheem 19 USER1 16-MAY-17 adriana 33 USER12 16-MAY-17 hasan 24 USER15 16-MAY-17 nazife 27 USER14 16-MAY-17 Ahmed 20 USER16 16-MAY-17 ugur 21 USER14 16-MAY-17 Ahmed 20 USER15 16-MAY-17 VELI 25 9 rows selected. SQL> update bff 2 set age=1 3 where name like 'A%'; 4 rows updated. SQL> select * from bff; NAME AGE ---------- ---------- molhim 22 ibraheem 19 adriana 33 hasan 24 AYSE 25 ALI 25 nazife 27 AHMED 25 ugur 21 AHMED 25 VELI 25 11 rows selected. SQL> select * from bff_audit; USERNAME MOD_DATE OPER PREV_NAME NEW_NAME PREV_AGE NEW_AGE ---------- --------- ------- ---------- ---------- ---------- ---------- USER17 16-MAY-17 molhim 22 USER10 16-MAY-17 ibraheem 19 USER1 16-MAY-17 adriana 33 USER12 16-MAY-17 hasan 24 USER15 16-MAY-17 nazife 27 USER14 16-MAY-17 Ahmed 20 USER16 16-MAY-17 ugur 21 USER14 16-MAY-17 Ahmed 20 USER15 16-MAY-17 VELI 25 USER15 16-MAY-17 AYSE AYSE 66 25 USER15 16-MAY-17 ALI ALI 55 25 USER15 16-MAY-17 Ahmed AHMED 20 25 USER15 16-MAY-17 Ahmed AHMED 20 25 13 rows selected. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t1 2 before insert or update or delete on bff 3 for each row 4 declare 5 v_oper varchar2(7); 6 begin 7 if inserting then 8 v_oper := 'insert'; 9 elsif deleting then 10 v_oper := 'delete'; 11 elsif updating then 12 v_oper := 'update'; 13 end if; 14 insert into bff_audit(username,mod_date,oper,prev_name,new_name,prev_age,new_age) 15 values(user,sysdate,v_oper,:old.name,:new.name,:old.age,:new.age); 16* end; 17 / Trigger created. SQL> update bff set name='Lima' where name='adriana'; 1 row updated. SQL> select * from bff; NAME AGE ---------- ---------- molhim 22 ibraheem 19 LIMA 33 hasan 24 AYSE 25 ALI 25 nazife 27 AHMED 25 ugur 21 AHMED 25 VELI 25 11 rows selected. SQL> select * from bff_audit; USERNAME MOD_DATE OPER PREV_NAME NEW_NAME PREV_AGE NEW_AGE ---------- --------- ------- ---------- ---------- ---------- ---------- USER17 16-MAY-17 molhim 22 USER10 16-MAY-17 ibraheem 19 USER1 16-MAY-17 adriana 33 USER12 16-MAY-17 hasan 24 USER15 16-MAY-17 nazife 27 USER14 16-MAY-17 Ahmed 20 USER16 16-MAY-17 ugur 21 USER14 16-MAY-17 Ahmed 20 USER15 16-MAY-17 VELI 25 USER15 16-MAY-17 AYSE AYSE 66 25 USER15 16-MAY-17 ALI ALI 55 25 USER15 16-MAY-17 Ahmed AHMED 20 25 USER15 16-MAY-17 Ahmed AHMED 20 25 USER15 16-MAY-17 update adriana LIMA 33 33 14 rows selected. SQL> spool off