SQL> --triggers SQL> create table bff( 2 name varchar2(10)); Table created. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t1 2 after insert on bff 3 declare 4 begin 5 dbms_output.put_line('someone is inserting into bff'); 6* end; SQL> / Trigger created. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t1 2 after insert on bff 3 declare 4 begin 5 dbms_output.put_line('someone is inserting into bff'); 6* end; SQL> ed Wrote file afiedt.buf 1 create or replace trigger t1 2 after insert on bff 3 begin 4 dbms_output.put_line('someone is inserting into bff'); 5* end; SQL> / Trigger created. SQL> insert into bff(name) 2 values('Ali'); 1 row created. SQL> set serveroutput on SQL> insert into bff(name) 2 values('Ali'); someone is inserting into bff 1 row created. SQL> insert into bff(name) 2 values('Ayse'); someone is inserting into bff 1 row created. SQL> delete from bff; 3 rows deleted. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t1 2 after insert on bff 3 begin 4 dbms_output.put_line(user||' is inserting into bff on '||sysdate); 5* end; SQL> / Trigger created. SQL> insert into bff(name) 2 values('Ayse'); USER15 is inserting into bff on 16-MAY-17 1 row created. SQL> grant all on bff to public; Grant succeeded. SQL> select * from bff; NAME ---------- veli Ayse NOOB SQL> select user from dual; USER ------------------------------ USER15 SQL> select * from bff; NAME ---------- veli veli Ayse NOOB HEHE SQL> select * from bff; NAME ---------- veli veli peter veli Ayse NOOB HEHE alex END OF LEC 9 rows selected. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t1 2 after insert or delete or update on bff 3 begin 4 dbms_output.put_line('statement level trigger'); 5* end; SQL> / Trigger created. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t2 2 after insert or delete or update on bff 3 for each row 4 begin 5 dbms_output.put_line('row level trigger'); 6* end; SQL> / Trigger created. SQL> revoke all on bff from public; Revoke succeeded. SQL> select * from bff; NAME ---------- Sulaiman usama usama veli veli peter veli Ahmed Alex Ayse NOOB NAME ---------- HEHE Elfageih alex END OF LEC Give break 16 rows selected. SQL> delete from bff where name='veli'; row level trigger row level trigger row level trigger statement level trigger 3 rows deleted. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t2 2 after insert or delete or update on bff 3 for each row 4 begin 5 dbms_output.put('row level trigger '); 6 dbms_output.put_line('old name='||:old.name||'new name='||:new.name||'*'); 7* end; 8 / Trigger created. SQL> select * from bff; NAME ---------- kamal Sulaiman Mustafa Mustafa usama Mustafa usama Can boy nenlep peter NAME ---------- Ahmed Alex Ayse NOOB HEHE Elfageih alex END OF LEC Give break 20 rows selected. SQL> update bff 2 set name='wes' 3 where name like 'A%'; row level trigger old name=Ahmednew name=wes* row level trigger old name=Alexnew name=wes* row level trigger old name=Aysenew name=wes* statement level trigger 3 rows updated. SQL> delete from bff where name like 'M%'; row level trigger old name=Mustafanew name=* row level trigger old name=Mustafanew name=* row level trigger old name=Mustafanew name=* statement level trigger 3 rows deleted. SQL> insert into bff 2 values('paye'); row level trigger old name=new name=paye* statement level trigger 1 row created. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t2 2 after insert or update on bff 3 for each row 4 begin 5 :new.name := 'nazife'; 6* end; 7 / create or replace trigger t2 * ERROR at line 1: ORA-04084: cannot change NEW values for this trigger type SQL> ed Wrote file afiedt.buf 1 create or replace trigger t2 2 before insert or update on bff 3 for each row 4 begin 5 :new.name := 'nazife'; 6* end; SQL> blockterminator "." (hex 2e) pagesize 14 pagesize 14 linesize 80 select * from bff; NAME ---------- kamal Sulaiman usama usama Can boy nenlep peter paye wes wes wes NOOB HEHE Elfageih alex END OF LEC Give break 18 rows selected. SQL> insert into bff 2 values('alex'); row level trigger old name=new name=alex* statement level trigger 1 row created. SQL> create or replace trigger t2 2 before insert or update on bff 3 for each row 4 begin 5 :new.name := 'nazife'; 6 end; 7 / Trigger created. SQL> insert into bff 2 values('mustafa'); statement level trigger 1 row created. SQL> select * from bff; NAME ---------- kamal Sulaiman usama usama Can boy nenlep peter paye wes wes wes NOOB HEHE Elfageih alex END OF LEC Give break alex nazife 20 rows selected. SQL> insert into bff 2 values('sadik'); statement level trigger 1 row created. SQL> select * from bff; NAME ---------- kamal Sulaiman usama usama Can boy nenlep peter paye wes wes wes NOOB HEHE Elfageih alex END OF LEC Give break alex nazife nazife 21 rows selected. SQL> -- modify the bff table to have age column SQL> alter table bff 2 add age number; Table altered. SQL> desc bff Name Null? Type ----------------------------------------- -------- ---------------------------- NAME VARCHAR2(10) AGE NUMBER SQL> drop trigger t1 2 ; Trigger dropped. SQL> drop trigger t2; Trigger dropped. SQL> SQL> create table audit_bff( 2 old_name VARCHAR2(10), 3 new_name VARCHAR2(10), 4 old_age number 5 new_age number, 6 oper varchar(7), 7 username varchar2(10), 8 mod_date date); new_age number, * ERROR at line 5: ORA-00907: missing right parenthesis SQL> 4 old_age number; SQL> l 1 create table audit_bff( 2 old_name VARCHAR2(10), 3 new_name VARCHAR2(10), 4 old_age number 5 new_age number, 6 oper varchar(7), 7 username varchar2(10), 8* mod_date date) SQL> ed Wrote file afiedt.buf 1 create table audit_bff( 2 old_name VARCHAR2(10), 3 new_name VARCHAR2(10), 4 old_age number, 5 new_age number, 6 oper varchar2(7), 7 username varchar2(10), 8* mod_date date) SQL> / Table created. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t1 2 after insert or delete or update on bff 3 for each row 4 declare 5 begin 6 insert into audit_bff(old_name,new_name,old_age,new_age,oper,username,mod_date) 7 values(:old.name,:new.name,:old.age,:new.age,null,user,sysdate); 8* end; 9 / Trigger created. SQL> select * from bff; NAME AGE ---------- ---------- Wes kamal Sulaiman Wes usama usama Can boy nenlep peter abobaker paye wes wes wes NOOB HEHE Elfageih alex END OF LEC Give break alex nazife nazife 24 rows selected. SQL> select * from audit_bff; no rows selected SQL> insert nto bff 2 values( 3 SQL> insert into bff 2 values('ayse',27); 1 row created. SQL> select * from bff; NAME AGE ---------- ---------- Wes kamal Sulaiman Wes usama usama Can boy nenlep peter abobaker paye wes wes wes NOOB HEHE Elfageih alex END OF LEC Give break alex nazife nazife ayse 27 25 rows selected. SQL> select * from audit_bff; OLD_NAME NEW_NAME OLD_AGE NEW_AGE OPER USERNAME MOD_DATE ---------- ---------- ---------- ---------- ------- ---------- --------- ayse 27 USER15 16-MAY-17 SQL> ed Wrote file afiedt.buf 1 create or replace trigger t1 2 after insert or delete or update 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 --we could use else here without a condition 12 v_oper:='update'; 13 end if; 14 insert into audit_bff(old_name,new_name,old_age,new_age,oper,username,mod_date) 15 values(:old.name,:new.name,:old.age,:new.age,v_oper,user,sysdate); 16* end; 17 / Trigger created. SQL> grant all on bff to public; Grant succeeded. SQL> --user16 is deleting nazife from bff table using the following command SQL> --delete from user15.bff where name='nazife'; SQL> --user14 is inserting tom into the bff table SQL> --insert into user15.bff values('tom', 33) SQL> --user 12 is updating SQL> --update the table so that all names that start with p are converted to upper case SQL> --update user15.bff set name=upper(name) where name like 'p%' SQL> select * from bff; NAME AGE ---------- ---------- Wes kamal Sulaiman Wes usama usama Can boy nenlep peter abobaker paye wes wes wes NOOB HEHE Elfageih alex END OF LEC Give break alex ayse 27 23 rows selected. SQL> select * from audit_bff; OLD_NAME NEW_NAME OLD_AGE NEW_AGE OPER USERNAME MOD_DATE ---------- ---------- ---------- ---------- ------- ---------- --------- nazife delete USER16 16-MAY-17 nazife delete USER16 16-MAY-17 ayse 27 USER15 16-MAY-17 SQL> select * from bff; NAME AGE ---------- ---------- Wes kamal Sulaiman Wes usama usama Can boy nenlep peter abobaker paye wes wes wes NOOB HEHE Elfageih alex END OF LEC Give break alex ayse 27 23 rows selected. SQL> select * from bff; NAME AGE ---------- ---------- Wes kamal Sulaiman Wes usama usama Can boy nenlep peter abobaker paye wes wes wes NOOB HEHE Elfageih alex END OF LEC Give break alex tom 33 ayse 27 tom 33 25 rows selected. SQL> select * from audit_bff; OLD_NAME NEW_NAME OLD_AGE NEW_AGE OPER USERNAME MOD_DATE ---------- ---------- ---------- ---------- ------- ---------- --------- nazife delete USER16 16-MAY-17 nazife delete USER16 16-MAY-17 ayse 27 USER15 16-MAY-17 tom 33 insert USER14 16-MAY-17 tom 33 insert USER14 16-MAY-17 SQL> select * from bff; NAME AGE ---------- ---------- Wes kamal Sulaiman Wes usama usama Can boy nenlep peter abobaker paye wes wes wes NOOB HEHE Elfageih alex END OF LEC Give break alex tom 33 ayse 27 tom 33 25 rows selected. SQL> select * from bff; NAME AGE ---------- ---------- Wes kamal Sulaiman Wes usama usama Can boy nenlep PETER abobaker PAYE wes wes wes NOOB HEHE Elfageih alex END OF LEC Give break alex tom 33 ayse 27 tom 33 25 rows selected. SQL> select * from audit_bff; OLD_NAME NEW_NAME OLD_AGE NEW_AGE OPER USERNAME MOD_DATE ---------- ---------- ---------- ---------- ------- ---------- --------- nazife delete USER16 16-MAY-17 nazife delete USER16 16-MAY-17 peter PETER update USER12 16-MAY-17 paye PAYE update USER12 16-MAY-17 ayse 27 USER15 16-MAY-17 tom 33 insert USER14 16-MAY-17 tom 33 insert USER14 16-MAY-17 7 rows selected. SQL> spool off