SQL> --triggers SQL> --triggers are programs that are executed whenever a certain event pccurs SQL> --triggers are executed automatically by the dbms engine SQL> SQL> --triggers are stored together with the tables SQL> create table movies( 2 title varchar2(10), 3 year number(4)); create table movies( * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> create table mymovies( 2 title varchar2(10), 3 year number(4)); Table created. SQL> SQL> create table mymovies_audit( 2 username varchar2(20), 3 modification_date date, 4 action varchar2(8), 5 old_title varchar2(10), 6 new_title varchar2(10)); Table created. SQL> desc mymovies Name Null? Type ----------------------------------------- -------- ---------------------------- TITLE VARCHAR2(10) YEAR NUMBER(4) SQL> desc mymovies_audit Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(20) MODIFICATION_DATE DATE ACTION VARCHAR2(8) OLD_TITLE VARCHAR2(10) NEW_TITLE VARCHAR2(10) SQL> select user from dual; USER ------------------------------ USER15 SQL> grant all on mymovies to user16; Grant succeeded. SQL> insert into my_movies 2 values('rouge 1', 2016); insert into my_movies * ERROR at line 1: ORA-00942: table or view does not exist SQL> insert into mymovies 2 values('rouge 1', 2016); 1 row created. SQL> select * from mymovies; TITLE YEAR ---------- ---------- rouge 1 2016 SQL> select * from mymovies; TITLE YEAR ---------- ---------- rouge 1 2016 SQL> select * from mymovies; TITLE YEAR ---------- ---------- rouge 1 2016 cursedch 2017 SQL> commit; Commit complete. SQL> select * from mymovies; TITLE YEAR ---------- ---------- rouge 1 2016 cursedch 2017 hp 2000 aaa 2001 SQL> select user from dual; USER ------------------------------ USER15 SQL> set serveroutput on SQL> ed Wrote file afiedt.buf 1 create or replace trigger t1 2 after insert or delete or update on mymovies 3 for each row 4 declare 5 begin 6 insert into mymovies_audit(username,modification_date,action,old_title,new_title) 7 values(); 8* end; 9 10 . SQL> desc mymovies_audit Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(20) MODIFICATION_DATE DATE ACTION VARCHAR2(8) OLD_TITLE VARCHAR2(10) NEW_TITLE VARCHAR2(10) SQL> ed Wrote file afiedt.buf 1 create or replace trigger t1 2 after insert or delete or update on mymovies 3 for each row 4 declare 5 v_command varchar2(8); 6 begin 7 if inserting then 8 v_command := 'insert'; 9 elsif updating then 10 v_command := 'update'; 11 else 12 v_command := 'delete'; 13 end; 14 insert into mymovies_audit(username,modification_date,action,old_title,new_title) 15 values(user,sysdate,v_command,:old.title,:new.title); 16* end; 17 / create or replace trigger t1 * ERROR at line 1: ORA-04095: trigger 'T1' already exists on another table, cannot replace it SQL> ed Wrote file afiedt.buf 1 create or replace trigger t_mymovies 2 after insert or delete or update on mymovies 3 for each row 4 declare 5 v_command varchar2(8); 6 begin 7 if inserting then 8 v_command := 'insert'; 9 elsif updating then 10 v_command := 'update'; 11 else 12 v_command := 'delete'; 13 end; 14 insert into mymovies_audit(username,modification_date,action,old_title,new_title) 15 values(user,sysdate,v_command,:old.title,:new.title); 16* end; SQL> / Warning: Trigger created with compilation errors. SQL> sho err Errors for TRIGGER T_MYMOVIES: LINE/COL ERROR -------- ----------------------------------------------------------------- 10/7 PLS-00103: Encountered the symbol ";" when expecting one of the following: if The symbol "if" was substituted for ";" to continue. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t_mymovies 2 after insert or delete or update on mymovies 3 for each row 4 declare 5 v_command varchar2(8); 6 begin 7 if inserting then 8 v_command := 'insert'; 9 elsif updating then 10 v_command := 'update'; 11 else 12 v_command := 'delete'; 13 end if; 14 insert into mymovies_audit(username,modification_date,action,old_title,new_title) 15 values(user,sysdate,v_command,:old.title,:new.title); 16* end; SQL> / Trigger created. SQL> insert into mymovies 2 values('qqqq',1999); 1 row created. SQL> select * from mymovies; TITLE YEAR ---------- ---------- rouge 1 2016 qqqq 1999 cursedch 2017 hp 2000 aaa 2001 SQL> select * from mymovies_audit; USERNAME MODIFICAT ACTION OLD_TITLE NEW_TITLE -------------------- --------- -------- ---------- ---------- USER15 21-DEC-17 insert qqqq SQL> grant all on mymovies to public; Grant succeeded. SQL> select user from dual; USER ------------------------------ USER15 SQL> select * from mymovies; TITLE YEAR ---------- ---------- rouge 1 2016 qqqq 1999 soroush 1994 cursedch 2017 hp 2000 aaa 2001 6 rows selected. SQL> select * from mymovies_audit; USERNAME MODIFICAT ACTION OLD_TITLE NEW_TITLE -------------------- --------- -------- ---------- ---------- USER15 21-DEC-17 insert qqqq USER17 21-DEC-17 insert soroush SQL> select * from mymovies; TITLE YEAR ---------- ---------- rouge 1 2016 qqqq 1999 soroush 1994 cursedch 2017 hp 2000 aaa 2001 6 rows selected. SQL> SQL> select * from mymovies; TITLE YEAR ---------- ---------- rouge 1 2016 qqqq 1999 soroush 1994 cursedch 2017 hp 2000 aaa 2001 kelvin 2050 7 rows selected. SQL> select * from mymovies_audit; USERNAME MODIFICAT ACTION OLD_TITLE NEW_TITLE -------------------- --------- -------- ---------- ---------- USER15 21-DEC-17 insert qqqq USER17 21-DEC-17 insert soroush USER19 21-DEC-17 insert kelvin SQL> select * from mymovies; TITLE YEAR ---------- ---------- rouge 1 2016 qqqq 1999 soroush 1994 cursedch 2017 hp 2000 aaa 2001 kelvin 2050 7 rows selected. SQL> select * from mymovies; TITLE YEAR ---------- ---------- rouge 1 2016 soroush 1994 Karl 2010 cursedch 2017 hp 2000 aaa 2001 kelvin 2050 7 rows selected. SQL> select * from mymovies_audit; USERNAME MODIFICAT ACTION OLD_TITLE NEW_TITLE -------------------- --------- -------- ---------- ---------- USER15 21-DEC-17 insert qqqq USER17 21-DEC-17 insert soroush USER18 21-DEC-17 insert Karl USER10 21-DEC-17 delete qqqq USER19 21-DEC-17 insert kelvin SQL> revoke all on mymovies from public; Revoke succeeded. SQL> edit Wrote file afiedt.buf 1 create or replace trigger t2_mymovies 2 before insert or update 3 for each row 4 declare 5 begin 6 :new.title := 'last jedi'; 7* end; 8 / for each row * ERROR at line 3: ORA-00969: missing ON keyword SQL> ed Wrote file afiedt.buf 1 create or replace trigger t2_mymovies 2 before insert or update on mymovies 3 for each row 4 declare 5 begin 6 :new.title := 'last jedi'; 7* end; SQL> / Trigger created. SQL> grant insert on mymovies to public; Grant succeeded. SQL> select * from mymovies; TITLE YEAR ---------- ---------- rouge 1 2016 soroush 1994 last jedi 17 Karl 2010 last jedi 18 cursedch 2017 hp 2000 aaa 2001 kelvin 2050 last jedi 19 10 rows selected. SQL> select * from mymovies_audit; USERNAME MODIFICAT ACTION OLD_TITLE NEW_TITLE -------------------- --------- -------- ---------- ---------- USER15 21-DEC-17 insert qqqq USER17 21-DEC-17 insert soroush USER17 21-DEC-17 insert last jedi USER18 21-DEC-17 insert Karl USER18 21-DEC-17 insert last jedi USER10 21-DEC-17 delete qqqq USER19 21-DEC-17 insert kelvin USER19 21-DEC-17 insert last jedi 8 rows selected. SQL> select * from mymovies; TITLE YEAR ---------- ---------- rouge 1 2016 soroush 1994 last jedi 17 Karl 2010 last jedi 18 cursedch 2017 hp 2000 aaa 2001 kelvin 2050 last jedi 19 last jedi 13 11 rows selected. SQL> select * from mymovies_audit; USERNAME MODIFICAT ACTION OLD_TITLE NEW_TITLE -------------------- --------- -------- ---------- ---------- USER15 21-DEC-17 insert qqqq USER17 21-DEC-17 insert soroush USER17 21-DEC-17 insert last jedi USER18 21-DEC-17 insert Karl USER18 21-DEC-17 insert last jedi USER10 21-DEC-17 delete qqqq USER19 21-DEC-17 insert kelvin USER19 21-DEC-17 insert last jedi USER13 21-DEC-17 insert last jedi 9 rows selected. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t2_mymovies 2 before insert or update on mymovies 3 for each row 4 declare 5 begin 6 :new.title := upper(:new.title); 7* end; SQL> / Trigger created. SQL> spool off