SQL> --triggers SQL> --triggers are programs that are automatically executed by the dbms engine whenever SQL> --a certain event occurs SQL> --the triggering events can be insert, delete or update SQL> --triggers are used to enforce business rules and audit table usage SQL> create table teams( 2 name varchar2(15), 3 id number(4)); Table created. SQL> select user from dual; USER ------------------------------ USER15 SQL> desc team ERROR: ORA-04043: object team does not exist SQL> desc teams Name Null? Type ----------------------------------------- -------- ---------------------------- NAME VARCHAR2(15) ID NUMBER(4) SQL> create teams_audit( 2 modification_date date, 3 command_used varchar2(8), 4 modified_by varchar2(8), 5 previous_name varchar2(17), 6 new_name varchar2(15)); create teams_audit( * ERROR at line 1: ORA-00901: invalid CREATE command SQL> create table teams_audit( 2 modification_date date, 3 command_used varchar2(8), 4 modified_by varchar2(8), 5 previous_name varchar2(17), 6 new_name varchar2(15)); Table created. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t1_teams 2 after insert or delete or update on teams 3 for each row 4 declare 5 v_command varchar2(8); 6 begin 7 if inserting then 8 v_command :='insert'; 9 elsif deleting then 10 v_command :='delete'; 11 else 12 v_command :='update'; 13 end if; 14 insert into teams_audit( modification_date, command_used, modified_by, previous_name, new_name) 15 values(sysdate,v_command,user, :old.name, :new.name ); 16* end; 17 / Trigger created. SQL> select * from teams; no rows selected SQL> select * from teams_audit; no rows selected SQL> insert into teams 2 values('EMUC',1); 1 row created. SQL> select * from teams; NAME ID --------------- ---------- EMUC 1 SQL> select * from teams_audit; MODIFICAT COMMAND_ MODIFIED PREVIOUS_NAME NEW_NAME --------- -------- -------- ----------------- --------------- 21-DEC-17 insert USER15 EMUC SQL> grant all on teams to public; Grant succeeded. SQL> select user from dual; USER ------------------------------ USER15 SQL> select * from teams; NAME ID --------------- ---------- EMUC 1 SQL> select * from teams; NAME ID --------------- ---------- EMUC 1 SQL> r 1* select * from teams NAME ID --------------- ---------- real madrid 4 EMUC 1 real madrid 7 besiktas 2 SQL> r 1* select * from teams NAME ID --------------- ---------- real madrid 4 liverpool 5 EMUC 1 Bercelona 3 real madrid 7 besiktas 2 6 rows selected. SQL> select * from teams_audit; MODIFICAT COMMAND_ MODIFIED PREVIOUS_NAME NEW_NAME --------- -------- -------- ----------------- --------------- 21-DEC-17 insert USER17 real madrid 21-DEC-17 insert USER18 liverpool 21-DEC-17 insert USER15 EMUC 21-DEC-17 insert USER10 Bercelona 21-DEC-17 insert USER11 real madrid 21-DEC-17 insert USER1 besiktas 6 rows selected. SQL> r 1* select * from teams_audit MODIFICAT COMMAND_ MODIFIED PREVIOUS_NAME NEW_NAME --------- -------- -------- ----------------- --------------- 21-DEC-17 insert USER17 real madrid 21-DEC-17 insert USER18 liverpool 21-DEC-17 insert USER15 EMUC 21-DEC-17 insert USER10 Bercelona 21-DEC-17 insert USER12 GALATASARAY 21-DEC-17 insert USER11 real madrid 21-DEC-17 insert USER1 besiktas 7 rows selected. SQL> select * from teams_audit 2 ; MODIFICAT COMMAND_ MODIFIED PREVIOUS_NAME NEW_NAME --------- -------- -------- ----------------- --------------- 21-DEC-17 insert USER17 real madrid 21-DEC-17 insert USER18 liverpool 21-DEC-17 insert USER18 Dortmund 21-DEC-17 insert USER15 EMUC 21-DEC-17 insert USER10 Bercelona 21-DEC-17 insert USER12 GALATASARAY 21-DEC-17 insert USER11 real madrid 21-DEC-17 insert USER1 besiktas 21-DEC-17 update USER1 real madrid besiktas 21-DEC-17 update USER1 real madrid besiktas 10 rows selected. SQL> select * from teams; NAME ID --------------- ---------- Bursaspor 5 Dortmund 8 GALATASARAY 5 Liverpool 7 SQL> select * from teams_audit; MODIFICAT COMMAND_ MODIFIED PREVIOUS_NAME NEW_NAME --------- -------- -------- ----------------- --------------- 21-DEC-17 insert USER17 real madrid 21-DEC-17 insert USER18 liverpool 21-DEC-17 insert USER18 Dortmund 21-DEC-17 update USER18 real madrid Liverpool 21-DEC-17 update USER18 real madrid Liverpool 21-DEC-17 update USER18 real madrid Liverpool 21-DEC-17 insert USER15 EMUC 21-DEC-17 insert USER10 Bercelona 21-DEC-17 insert USER12 GALATASARAY 21-DEC-17 update USER12 besiktas real madrid 21-DEC-17 update USER12 besiktas real madrid MODIFICAT COMMAND_ MODIFIED PREVIOUS_NAME NEW_NAME --------- -------- -------- ----------------- --------------- 21-DEC-17 update USER12 besiktas real madrid 21-DEC-17 update USER12 liverpool Bursaspor 21-DEC-17 insert USER11 real madrid 21-DEC-17 insert USER1 besiktas 21-DEC-17 update USER1 real madrid besiktas 21-DEC-17 update USER1 real madrid besiktas 21-DEC-17 delete USER1 Liverpool 21-DEC-17 delete USER1 EMUC 21-DEC-17 delete USER1 Bercelona 21-DEC-17 delete USER1 Liverpool 21 rows selected. SQL> SQL> select * from teams; NAME ID --------------- ---------- Bursaspor 5 Dortmund 8 Faisal 3 GALATASARAY 5 Liverpool 7 qqqq 2 rrrr 1 7 rows selected. SQL> select * from teams_audit; MODIFICAT COMMAND_ MODIFIED PREVIOUS_NAME NEW_NAME --------- -------- -------- ----------------- --------------- 21-DEC-17 insert USER17 real madrid 21-DEC-17 insert USER18 liverpool 21-DEC-17 insert USER18 Dortmund 21-DEC-17 update USER18 real madrid Liverpool 21-DEC-17 update USER18 real madrid Liverpool 21-DEC-17 update USER18 real madrid Liverpool 21-DEC-17 insert USER18 Faisal 21-DEC-17 insert USER15 EMUC 21-DEC-17 insert USER10 Bercelona 21-DEC-17 insert USER12 GALATASARAY 21-DEC-17 update USER12 besiktas real madrid MODIFICAT COMMAND_ MODIFIED PREVIOUS_NAME NEW_NAME --------- -------- -------- ----------------- --------------- 21-DEC-17 update USER12 besiktas real madrid 21-DEC-17 update USER12 besiktas real madrid 21-DEC-17 update USER12 liverpool Bursaspor 21-DEC-17 insert USER11 real madrid 21-DEC-17 insert USER1 besiktas 21-DEC-17 update USER1 real madrid besiktas 21-DEC-17 update USER1 real madrid besiktas 21-DEC-17 delete USER1 Liverpool 21-DEC-17 delete USER1 EMUC 21-DEC-17 delete USER1 Bercelona 21-DEC-17 delete USER1 Liverpool MODIFICAT COMMAND_ MODIFIED PREVIOUS_NAME NEW_NAME --------- -------- -------- ----------------- --------------- 21-DEC-17 insert USER1 qqqq 21-DEC-17 insert USER1 rrrr 24 rows selected. SQL> revoke all on teams from public; Revoke succeeded. SQL> ed Wrote file afiedt.buf 1 create or replace trigger t2_teams 2 before insert or update on teams 3 for each row 4 declare 5 begin 6 :new.name := 'Besiktas'; 7* end; SQL> / Trigger created. SQL> grant insert, update to public; grant insert, update to public * ERROR at line 1: ORA-01924: role 'INSERT' not granted or does not exist SQL> grant insert, update on teams to public; Grant succeeded. SQL> select * from teams; NAME ID --------------- ---------- Besiktas 90 Bursaspor 5 Dortmund 8 Faisal 3 Besiktas 4 GALATASARAY 5 Besiktas 3 qqqq 2 rrrr 1 Besiktas 2 10 rows selected. SQL> select * from teams; NAME ID --------------- ---------- Besiktas 90 Bursaspor 5 Dortmund 8 Faisal 3 Besiktas 4 GALATASARAY 5 Besiktas 3 qqqq 2 rrrr 1 Besiktas 2 10 rows selected. SQL> select * from teams_audit; MODIFICAT COMMAND_ MODIFIED PREVIOUS_NAME NEW_NAME --------- -------- -------- ----------------- --------------- 21-DEC-17 insert USER17 real madrid 21-DEC-17 insert USER18 liverpool 21-DEC-17 insert USER18 Dortmund 21-DEC-17 update USER18 real madrid Liverpool 21-DEC-17 update USER18 real madrid Liverpool 21-DEC-17 update USER18 real madrid Liverpool 21-DEC-17 insert USER18 Faisal 21-DEC-17 insert USER18 Besiktas 21-DEC-17 insert USER17 Besiktas 21-DEC-17 insert USER15 EMUC 21-DEC-17 insert USER10 Bercelona MODIFICAT COMMAND_ MODIFIED PREVIOUS_NAME NEW_NAME --------- -------- -------- ----------------- --------------- 21-DEC-17 insert USER12 GALATASARAY 21-DEC-17 update USER12 besiktas real madrid 21-DEC-17 update USER12 besiktas real madrid 21-DEC-17 update USER12 besiktas real madrid 21-DEC-17 update USER12 liverpool Bursaspor 21-DEC-17 delete USER10 Liverpool 21-DEC-17 insert USER10 Besiktas 21-DEC-17 insert USER11 real madrid 21-DEC-17 insert USER1 besiktas 21-DEC-17 update USER1 real madrid besiktas 21-DEC-17 update USER1 real madrid besiktas MODIFICAT COMMAND_ MODIFIED PREVIOUS_NAME NEW_NAME --------- -------- -------- ----------------- --------------- 21-DEC-17 delete USER1 Liverpool 21-DEC-17 delete USER1 EMUC 21-DEC-17 delete USER1 Bercelona 21-DEC-17 delete USER1 Liverpool 21-DEC-17 insert USER1 qqqq 21-DEC-17 insert USER1 rrrr 21-DEC-17 insert USER1 Besiktas 29 rows selected. SQL> delete from teams_audit; 29 rows deleted. SQL> select * from teams; NAME ID --------------- ---------- Besiktas 90 Bursaspor 5 Dortmund 8 Faisal 3 Besiktas 4 GALATASARAY 5 Besiktas 3 qqqq 2 rrrr 1 Besiktas 2 10 rows selected. SQL> select * from teams_audit; no rows selected SQL> select * from teams; NAME ID --------------- ---------- Besiktas 90 Bursaspor 5 Dortmund 8 Faisal 3 Besiktas 4 Besiktas 11 GALATASARAY 5 Besiktas 3 qqqq 2 rrrr 1 Besiktas 2 11 rows selected. SQL> select * from teams_audit; MODIFICAT COMMAND_ MODIFIED PREVIOUS_NAME NEW_NAME --------- -------- -------- ----------------- --------------- 21-DEC-17 insert USER17 Besiktas SQL> select * from teams; NAME ID --------------- ---------- Besiktas 90 Besiktas 6 Dortmund 8 Besiktas 6 Besiktas 6 Besiktas 11 Besiktas 6 Besiktas 6 Besiktas 6 Besiktas 6 Besiktas 6 11 rows selected. SQL> spool off