SQL> --triggers SQL> --ý will create a new table SQL> desc employees Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SQL> create table friends as 2 select FIRST_NAMEöPHONE_NUMBER 3 from employees 4 where department_id<60 5 ; select FIRST_NAMEöPHONE_NUMBER * ERROR at line 2: ORA-00904: "FIRST_NAMEÖPHONE_NUMBER": invalid identifier SQL> ed Wrote file afiedt.buf 1 create table friends as 2 select FIRST_NAME,PHONE_NUMBER 3 from employees 4* where department_id<60 5 / select FIRST_NAME,PHONE_NUMBER * ERROR at line 2: ORA-12152: TNS:unable to send break message SQL> / ERROR: ORA-03114: not connected to ORACLE SQL> conn user15/user15@orcl Connected. SQL> create table friends as 2 select FIRST_NAME,PHONE_NUMBER 3 from employees 4 where department_id<60þ 5 SQL> create table friends as 2 select FIRST_NAME,PHONE_NUMBER 3 from employees 4 where department_id<60; create table friends as * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> create table bffs as 2 select FIRST_NAME,PHONE_NUMBER 3 from employees 4 where department_id<60; Table created. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Den 515.127.4561 Alexander 515.127.4562 Shelli 515.127.4563 Sigal 515.127.4564 Guy 515.127.4565 Karen 515.127.4566 Matthew 650.123.1234 Adam 650.123.2234 Payam 650.123.3234 Shanta 650.123.4234 Kevin 650.123.5234 FIRST_NAME PHONE_NUMBER -------------------- -------------------- Julia 650.124.1214 Irene 650.124.1224 James 650.124.1334 Steven 650.124.1434 Laura 650.124.5234 Mozhe 650.124.6234 James 650.124.7234 TJ 650.124.8234 Jason 650.127.1934 Michael 650.127.1834 Ki 650.127.1734 FIRST_NAME PHONE_NUMBER -------------------- -------------------- Hazel 650.127.1634 Renske 650.121.1234 Stephen 650.121.2034 John 650.121.2019 Joshua 650.121.1834 Trenna 650.121.8009 Curtis 650.121.2994 Randall 650.121.2874 Peter 650.121.2004 Winston 650.507.9876 Jean 650.507.9877 FIRST_NAME PHONE_NUMBER -------------------- -------------------- Martha 650.507.9878 Girard 650.507.9879 Nandita 650.509.1876 Alexis 650.509.2876 Julia 650.509.3876 Anthony 650.509.4876 Kelly 650.505.1876 Jennifer 650.505.2876 Timothy 650.505.3876 Randall 650.505.4876 Sarah 650.501.1876 FIRST_NAME PHONE_NUMBER -------------------- -------------------- Britney 650.501.2876 Samuel 650.501.3876 Vance 650.501.4876 Alana 650.507.9811 Kevin 650.507.9822 Donald 650.507.9833 Douglas 650.507.9844 Jennifer 515.123.4444 Michael 515.123.5555 Pat 603.123.6666 Susan 515.123.7777 55 rows selected. SQL> drop table bffs; Table dropped. SQL> create table bffs as 2 select FIRST_NAME,PHONE_NUMBER 3 from employees 4 where department_id<30; Table created. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 515.123.4444 Michael 515.123.5555 Pat 603.123.6666 SQL> --wrýte a trýgger that wýll be fired whenever a bff is modified SQL> --the trigger wýll just prýnt a message on screen SQL> --the message will say "someone is updating the bff table" SQL> ed Wrote file afiedt.buf 1 --statement level after update trigger 2 create or replace trigger bffs_t1 3 after update on bffs 4 declare 5 begin 6 dbms_output.put_line("someone is updating bffs table"); 7* end; SQL> / Warning: Trigger created with compilation errors. SQL> sho err Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA SOURCE | JAVA CLASS } [schema.]name] SQL> show err Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA SOURCE | JAVA CLASS } [schema.]name] SQL> set serveroutput on SQL> ed Wrote file afiedt.buf 1 create or replace trigger bffs_t1 2 after update on bffs 3 declare 4 begin 5 dbms_output.put_line('someone is updating bffs table'); 6* end; SQL> ed Wrote file afiedt.buf 1 create or replace trigger bffs_t1 2 after update on bffs 3 declare 4 begin 5 dbms_output.put_line('someone is updating bffs table'); 6* end; SQL> / Trigger created. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 515.123.4444 Michael 515.123.5555 Pat 603.123.6666 SQL> insert into bffs 2 values('ali','11111'); 1 row created. SQL> update bffs 2 set PHONE_NUMBER='22222' 3 where FIRST_NAME='ali'; someone is updating bffs table 1 row updated. SQL> update bffs 2 set PHONE_NUMBER='22222'; someone is updating bffs table 4 rows updated. SQL> ed Wrote file afiedt.buf 1 create or replace trigger bffs_t2 2 after update on bffs 3 for each row 4 declare 5 begin 6 dbms_output.put_line('how many times is this trigger fired'); 7* end; SQL> / Trigger created. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 22222 Michael 22222 Pat 22222 ali 22222 SQL> update bffs 2 set PHONE_NUMBER='1111'; how many times is this trigger fired how many times is this trigger fired how many times is this trigger fired how many times is this trigger fired someone is updating bffs table 4 rows updated. SQL> --ed SQL> ed Wrote file afiedt.buf 1 create or replace trigger bffs_t1 2 after update on bffs 3 declare 4 begin 5 dbms_output.put_line(USER ||' is updating bffs table on '||sysdate); 6* end; SQL> / Trigger created. SQL> update bffs 2 set PHONE_NUMBER='1111'; how many times is this trigger fired how many times is this trigger fired how many times is this trigger fired how many times is this trigger fired USER15 is updating bffs table on 28-MAY-19 4 rows updated. SQL> --modify the statement level trigger so that you audit the bffs table SQL> whenever someone is inserting deleting or updating the bffs table store their info in the Usage: WHENEVER SQLERROR { CONTINUE [ COMMIT | ROLLBACK | NONE ] | EXIT [ SUCCESS | FAILURE | WARNING | n | | : ] [ COMMIT | ROLLBACK ] } Usage: WHENEVER OSERROR { CONTINUE [ COMMIT | ROLLBACK | NONE ] | EXIT [ SUCCESS | FAILURE | WARNING | n | | : | OSCODE ] [ COMMIT | ROLLBACK ] } SQL> --modify the statement level trigger so that you audit the bffs table SQL> --whenever someone is inserting deleting or updating the bffs table store their info in the SQL> --bffs_audit table SQL> create table bffs_audit as 2 SQL> desc bffs Name Null? Type ----------------------------------------- -------- ---------------------------- FIRST_NAME VARCHAR2(20) PHONE_NUMBER VARCHAR2(20) SQL> create table bffs_audit( 2 username varchar2(10), 3 moddate date, 4 commandused varchar2(10)) 5 ; Table created. SQL> desc bffs Name Null? Type ----------------------------------------- -------- ---------------------------- FIRST_NAME VARCHAR2(20) PHONE_NUMBER VARCHAR2(20) SQL> desc bffs_audit Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(10) MODDATE DATE COMMANDUSED VARCHAR2(10) SQL> ed Wrote file afiedt.buf 1 create or replace trigger bffs_t1 2 after update or delete or insert on bffs 3 declare 4 begin 5 insert into bffs_audit(username,moddate,commandused) 6 values(user,sysdate,'idonno'); 7* end; 8 / Trigger created. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 1111 Michael 1111 Pat 1111 ali 1111 SQL> select * from bffs_audit; no rows selected SQL> drop trigger bffs_t2; Trigger dropped. SQL> grant all on bffs to public; Grant succeeded. SQL> select user from dual; USER ------------------------------ USER15 SQL> insert into bffs 2 values('ayse','11344'); 1 row created. SQL> select user from dual; USER ------------------------------ USER15 SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 1111 Michael 1111 Pat 1111 ali 1111 ayse 11344 SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE ---------- --------- ---------- USER15 28-MAY-19 idonno SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 1111 Michael 1111 Pat 1111 ali 1111 ayse 11344 SQL> commit; Commit complete. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 1111 Michael 1111 Pat 1111 ali 1111 ayse 11344 SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 1111 Michael 1111 Pat 1111 paye 99999 ali 1111 ayse 11344 6 rows selected. SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE ---------- --------- ---------- USER24 28-MAY-19 idonno USER15 28-MAY-19 idonno SQL> ed Wrote file afiedt.buf 1 create or replace trigger bffs_t1 2 after update or delete or insert on bffs 3 declare 4 v_cname varchar2(10); 5 begin 6 if UPDATING then 7 v_cname := 'UPDATE'; 8 elsif DELETING then 9 v_cname := 'DELETE'; 10 else 11 v_cname := 'INSERT'; 12 end if; 13 insert into bffs_audit(username,moddate,commandused) 14 values(user,sysdate,v_cname); 15* end; 16 / Trigger created. SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE ---------- --------- ---------- USER24 28-MAY-19 idonno USER2 28-MAY-19 idonno USER15 28-MAY-19 idonno SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 1111 Michael 1111 Pat 1111 paye 99999 fatima 11111 ali 1111 ayse 11344 7 rows selected. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 1111 Michael 1111 Pat 1111 paye 99999 fatima 11111 banan 11451 ali 1111 ayse 11344 8 rows selected. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 1111 Michael 1111 Pat 1111 paye 99999 fatima 11111 banan 11451 ali 1111 ayse 11344 8 rows selected. SQL> / FIRST_NAME PHONE_NUMBER -------------------- -------------------- papaya 1111 papaya 1111 papaya 1111 paye 99999 fatima 11111 banan 11451 papaya 1111 ayse 11344 8 rows selected. SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE ---------- --------- ---------- USER24 28-MAY-19 idonno USER11 28-MAY-19 UPDATE USER2 28-MAY-19 idonno USER2 28-MAY-19 INSERT USER15 28-MAY-19 idonno SQL> SQL> drop trigger bffs_t1; Trigger dropped. SQL> desc bffs_audit Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(10) MODDATE DATE COMMANDUSED VARCHAR2(10) SQL> alter table bffs_audit 2 add(old_name VARCHAR2(10), new_name VARCHAR2(10)); Table altered. SQL> desc bffs_audit Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(10) MODDATE DATE COMMANDUSED VARCHAR2(10) OLD_NAME VARCHAR2(10) NEW_NAME VARCHAR2(10) SQL> --wri,te a trigger that will be fired whenever bff table is modified and populate SQL> --the beffs_audit table SQL> desc bffs Name Null? Type ----------------------------------------- -------- ---------------------------- FIRST_NAME VARCHAR2(20) PHONE_NUMBER VARCHAR2(20) SQL> desc bffs_audit Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(10) MODDATE DATE COMMANDUSED VARCHAR2(10) OLD_NAME VARCHAR2(10) NEW_NAME VARCHAR2(10) SQL> --triggerring event:update or insert or delete SQL> --timing:after SQL> --table:bffs SQL> --granularity (level):row level SQL> ed Wrote file afiedt.buf 1 create or replace trigger bffs_t2 2 after delete or insert or update on bffs 3 for each row 4 declare 5 v_command varchar2(10); 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 bffs_audit 15 values(user,sysdate,v_command,:old.first_name,:new.first_name); 16* end; 17 / Trigger created. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- paye 99999 fatima 11111 banan 11451 ayse 11344 SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER24 28-MAY-19 idonno USER11 28-MAY-19 UPDATE USER2 28-MAY-19 idonno USER2 28-MAY-19 INSERT USER2 28-MAY-19 DELETE USER15 28-MAY-19 idonno 6 rows selected. SQL> update bffs 2 set first_name = upper(first_name); 4 rows updated. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- PAYE 99999 FATIMA 11111 BANAN 11451 AYSE 11344 SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER24 28-MAY-19 idonno USER11 28-MAY-19 UPDATE USER2 28-MAY-19 idonno USER2 28-MAY-19 INSERT USER2 28-MAY-19 DELETE USER15 28-MAY-19 idonno USER15 28-MAY-19 update paye PAYE USER15 28-MAY-19 update fatima FATIMA USER15 28-MAY-19 update banan BANAN USER15 28-MAY-19 update ayse AYSE 10 rows selected. SQL> ed Wrote file afiedt.buf 1 create or replace trigger bffs_t2 2 before insert on bffs 3 for each row 4 declare 5 begin 6 :new.first_name := 'No ONE'; 7* end; SQL> / Trigger created. SQL> insert into bffs 2 values('Faeezah','7777'); 1 row created. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- PAYE 99999 FATIMA 11111 BANAN 11451 AYSE 11344 No ONE 7777 SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER24 28-MAY-19 idonno USER11 28-MAY-19 UPDATE USER2 28-MAY-19 idonno USER2 28-MAY-19 INSERT USER2 28-MAY-19 DELETE USER15 28-MAY-19 idonno USER15 28-MAY-19 update paye PAYE USER15 28-MAY-19 update fatima FATIMA USER15 28-MAY-19 update banan BANAN USER15 28-MAY-19 update ayse AYSE 10 rows selected. SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER24 28-MAY-19 idonno USER11 28-MAY-19 UPDATE USER2 28-MAY-19 idonno USER2 28-MAY-19 INSERT USER2 28-MAY-19 DELETE USER15 28-MAY-19 idonno USER15 28-MAY-19 update paye PAYE USER15 28-MAY-19 update fatima FATIMA USER15 28-MAY-19 update banan BANAN USER15 28-MAY-19 update ayse AYSE 10 rows selected. SQL> commit; Commit complete. SQL> / Commit complete. SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER24 28-MAY-19 idonno USER11 28-MAY-19 UPDATE USER2 28-MAY-19 idonno USER2 28-MAY-19 INSERT USER2 28-MAY-19 DELETE USER15 28-MAY-19 idonno USER15 28-MAY-19 update paye PAYE USER15 28-MAY-19 update fatima FATIMA USER15 28-MAY-19 update banan BANAN USER15 28-MAY-19 update ayse AYSE 10 rows selected. SQL> spool off