SQL> create table bff as 2 select first_name,phone_number from employees where department_id <=30; create table bff as * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> drop table bff 2 ; Table dropped. SQL> drop table bffs 2 ; Table dropped. SQL> create table bffs 2 as 3 select first_name,phone_number from employees 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 Den 515.127.4561 Alexander 515.127.4562 Shelli 515.127.4563 Sigal 515.127.4564 Guy 515.127.4565 Karen 515.127.4566 9 rows selected. SQL> truncate bffs_audit; truncate bffs_audit * ERROR at line 1: ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword SQL> truncate table bffs_audit; Table truncated. SQL> desc bffs_audit Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(10) MODDATE DATE COMMANDUSED VARCHAR2(10) OLD_NAME VARCHAR2(10) NEW_NAME VARCHAR2(10) SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 515.123.4444 Michael 515.123.5555 Pat 603.123.6666 Den 515.127.4561 Alexander 515.127.4562 Shelli 515.127.4563 Sigal 515.127.4564 Guy 515.127.4565 Karen 515.127.4566 9 rows selected. SQL> select * from bffs_audit; no rows selected SQL> --write a trigger that is fired whenever the bffs are modified and populate the SQL> --bffs_audit table SQL> --(do nnot worry about old name and new name) SQL> --user name ýs the oracle user who ýs doýng the modification SQL> --mod date is the date SQL> --command used will be idonno SQL> --newname and oldname will be null SQL> SQL> SQL> --triggerring event: update SQL> --timing:after SQL> --table:bffs SQL> --granularity:statement level SQL> ed Wrote file afiedt.buf 1 create or replace trigger bffs_autit_trig 2 after update on bffs 3 declare 4 begin 5 insert into bffs_audit( 6* end; 7 ed 8 . SQL> ed Wrote file afiedt.buf 1 create or replace trigger bffs_audit_trig 2 after update on bffs 3 declare 4 begin 5 insert into bffs_audit( USERNAME,MODDATE,COMMANDUSED,OLD_NAME,NEW_NAME) 6 values(user,sysdate,'idonno',null,null); 7* end; 8 / Trigger created. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 515.123.4444 Michael 515.123.5555 Pat 603.123.6666 Den 515.127.4561 Alexander 515.127.4562 Shelli 515.127.4563 Sigal 515.127.4564 Guy 515.127.4565 Karen 515.127.4566 9 rows selected. SQL> select * from bffs_audit; no rows selected SQL> insert into bffs values('ali','333'); 1 row created. SQL> --insert statment does NOTTTT cause bffs_audit_trig to fire SQL> select * from bffs_audit; no rows selected SQL> update bffs 2 set FIRST_NAME='veli' where FIRST_NAME='Pat'; 1 row updated. SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER15 28-MAY-19 idonno SQL> --modify the trigger bffs_audit_trig so that SQL> --it is fired for insert delete and update SQL> --And when populating the bffs_audit table set the command used to the relevant SQL> --triggering event SQL> ed Wrote file afiedt.buf 1 create or replace trigger bffs_audit_trig 2 after update or delete or insert on bffs 3 declare 4 v_command varchar2(10); 5 begin 6 if updating then 7 v_command :='update'; 8 elsif deleting then 9 v_command :='delete'; 10 else 11 v_command :='insert'; 12 END if; 13 insert into bffs_audit( USERNAME,MODDATE,COMMANDUSED,OLD_NAME,NEW_NAME) 14 values(user,sysdate,v_command,null,null); 15* end; 16 / Trigger created. SQL> select user from dual; USER ------------------------------ USER15 SQL> grant all on bffs to public; Grant succeeded. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 515.123.4444 Michael 515.123.5555 veli 603.123.6666 Den 515.127.4561 Alexander 515.127.4562 Shelli 515.127.4563 Sigal 515.127.4564 Guy 515.127.4565 Karen 515.127.4566 ali 333 10 rows selected. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 515.123.4444 Michael 515.123.5555 veli 603.123.6666 Den 515.127.4561 Alexander 515.127.4562 Shelli 515.127.4563 Sigal 515.127.4564 Guy 515.127.4565 Karen 515.127.4566 nazife 66778 ali 333 11 rows selected. SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER24 28-MAY-19 insert USER15 28-MAY-19 idonno SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER24 28-MAY-19 insert USER1 28-MAY-19 insert USER8 28-MAY-19 insert USER15 28-MAY-19 idonno USER14 28-MAY-19 insert USER18 28-MAY-19 insert USER19 28-MAY-19 insert 7 rows selected. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 515.123.4444 Michael 515.123.5555 veli 603.123.6666 Den 515.127.4561 Alexander 515.127.4562 Shelli 515.127.4563 Sigal 515.127.4564 Guy 515.127.4565 Karen 515.127.4566 nazife 66778 rosshdy 212112121 FIRST_NAME PHONE_NUMBER -------------------- -------------------- favour 3222 kareem 42069 Temidayo 0533840 ali 333 Donald 26879 Yussuf 12345 17 rows selected. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 515.123.4444 Michael 515.123.5555 veli 603.123.6666 Den 515.127.4561 Alexander 515.127.4562 Shelli 515.127.4563 Sigal 515.127.4564 Guy 515.127.4565 Karen 515.127.4566 nazife 66778 rosshdy 212112121 FIRST_NAME PHONE_NUMBER -------------------- -------------------- favour 3222 kareem 42069 sakina 22223 Temidayo 0533840 sakina 22223 ali 333 Donald 26879 ify 12345 shahriar 17700062 Yussuf 12345 omar 545255655 FIRST_NAME PHONE_NUMBER -------------------- -------------------- amirhossein 4464654 23 rows selected. SQL> / FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 515.123.4444 Michael 515.123.5555 veli 603.123.6666 Den 515.127.4561 Alexander 515.127.4562 Shelli 515.127.4563 Sigal 515.127.4564 Guy 515.127.4565 Karen 515.127.4566 nazife 66778 rosshdy 212112121 FIRST_NAME PHONE_NUMBER -------------------- -------------------- favour 3222 kareem 42069 sakina 22223 Temidayo 0533840 sakina 22223 ali 333 Donald 26879 ify 12345 shahriar 17700062 Yussuf 12345 omar 545255655 FIRST_NAME PHONE_NUMBER -------------------- -------------------- amirhossein 4464654 23 rows selected. SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER24 28-MAY-19 insert USER1 28-MAY-19 insert USER2 28-MAY-19 insert USER11 28-MAY-19 insert USER8 28-MAY-19 insert USER11 28-MAY-19 insert USER17 28-MAY-19 insert USER16 28-MAY-19 insert USER15 28-MAY-19 idonno USER12 28-MAY-19 insert USER13 28-MAY-19 insert USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER14 28-MAY-19 insert USER18 28-MAY-19 insert USER19 28-MAY-19 insert 14 rows selected. SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER24 28-MAY-19 insert USER1 28-MAY-19 insert USER2 28-MAY-19 insert USER11 28-MAY-19 insert USER8 28-MAY-19 insert USER11 28-MAY-19 insert USER17 28-MAY-19 insert USER16 28-MAY-19 insert USER17 28-MAY-19 delete USER15 28-MAY-19 idonno USER12 28-MAY-19 insert USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER13 28-MAY-19 insert USER14 28-MAY-19 insert USER18 28-MAY-19 insert USER19 28-MAY-19 insert 15 rows selected. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 515.123.4444 Michael 515.123.5555 veli 603.123.6666 Den 515.127.4561 Alexander 515.127.4562 Shelli 515.127.4563 Sigal 515.127.4564 Guy 515.127.4565 Karen 515.127.4566 nazife 66778 rosshdy 212112121 FIRST_NAME PHONE_NUMBER -------------------- -------------------- favour 3222 kareem 42069 sakina 22223 Temidayo 0533840 sakina 22223 ali 333 Donald 26879 ify 12345 shahriar 17700062 Yussuf 12345 omar 545255655 FIRST_NAME PHONE_NUMBER -------------------- -------------------- amirhossein 4464654 23 rows selected. SQL> / FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 515.123.4444 Michael 515.123.5555 veli 603.123.6666 Den 515.127.4561 Alexander 515.127.4562 Shelli 515.127.4563 Sigal 515.127.4564 Guy 515.127.4565 Karen 515.127.4566 nazife 66778 favour 3222 FIRST_NAME PHONE_NUMBER -------------------- -------------------- kareem 42069 Temidayo 0533840 ali 333 ify 12345 shahriar 17700062 Yussuf 12345 omar 545255655 amirhossein 4464654 19 rows selected. SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER24 28-MAY-19 insert USER1 28-MAY-19 insert USER2 28-MAY-19 insert USER24 28-MAY-19 delete USER11 28-MAY-19 insert USER8 28-MAY-19 insert USER11 28-MAY-19 insert USER17 28-MAY-19 insert USER16 28-MAY-19 insert USER17 28-MAY-19 delete USER15 28-MAY-19 idonno USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER12 28-MAY-19 insert USER13 28-MAY-19 insert USER14 28-MAY-19 insert USER18 28-MAY-19 insert USER19 28-MAY-19 insert 16 rows selected. SQL> desc bffs_audit Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(10) MODDATE DATE COMMANDUSED VARCHAR2(10) OLD_NAME VARCHAR2(10) NEW_NAME VARCHAR2(10) SQL> --remember if you need to have access to row ýn a trýgger you must use row level trýgger SQL> -- SQL> -- SQL> -- write a trigger that populates bffs_audit table for all DML operations SQL> --note that you must insert the old name and new name ýn the modified rows SQL> desc bffs_audit Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(10) MODDATE DATE COMMANDUSED VARCHAR2(10) OLD_NAME VARCHAR2(10) NEW_NAME VARCHAR2(10) SQL> desc bffs Name Null? Type ----------------------------------------- -------- ---------------------------- FIRST_NAME VARCHAR2(20) PHONE_NUMBER VARCHAR2(20) SQL> --to answer this question we must find out the firstname value SQL> --we need to see the previous image and after image of eac modified row ==> row level trigger SQL> ed Wrote file afiedt.buf 1 create or replace trigger bffs_audit_trig 2 after update or delete or insert on bffs 3 for each row 4 declare 5 v_command varchar2(10); 6 begin 7 if updating then 8 v_command :='update'; 9 elsif deleting then 10 v_command :='delete'; 11 else 12 v_command :='insert'; 13 END if; 14 insert into bffs_audit( USERNAME,MODDATE,COMMANDUSED,OLD_NAME,NEW_NAME) 15 values(user,sysdate,v_command,:old.first_name,:new.first_name); 16* end; SQL> / Trigger created. SQL> delete from bffs_audit 2 ; 16 rows deleted. SQL> set pagesize 120 SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 515.123.4444 Michael 515.123.5555 veli 603.123.6666 Den 515.127.4561 Alexander 515.127.4562 Shelli 515.127.4563 Sigal 515.127.4564 Guy 515.127.4565 Karen 515.127.4566 nazife 66778 favour 3222 kareem 42069 Temidayo 0533840 ali 333 ify 12345 shahriar 17700062 Yussuf 12345 omar 545255655 amirhossein 4464654 19 rows selected. SQL> select * from bffs_audit; no rows selected SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- Jennifer 515.123.4444 Michael 515.123.5555 veli 603.123.6666 Den 515.127.4561 Alexander 515.127.4562 Shelli 515.127.4563 Sigal 515.127.4564 Guy 515.127.4565 Karen 515.127.4566 nazife 66778 favour 3222 temidayo 789 kareem 42069 Temidayo 0533840 ali 333 ify 12345 shahriar 17700062 Yussuf 12345 omar 545255655 amirhossein 4464654 20 rows selected. SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER24 28-MAY-19 insert temidayo SQL> --when inserting old row contains NULL values SQL> --when deleting new values are NULL SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- qswd 515.123.4444 qswd 515.123.5555 veli 603.123.6666 qswd 515.127.4561 qswd 515.127.4562 qswd 515.127.4563 qswd 515.127.4564 qswd 515.127.4565 qswd 515.127.4566 nazife 66778 favour 3222 temidayo 789 kareem 42069 Temidayo 0533840 ali 333 ify 12345 shahriar 17700062 Yussuf 12345 qswd 545255655 amirhossein 4464654 20 rows selected. SQL> select * from bffs_audit; USERNAME MODDATE COMMANDUSE OLD_NAME NEW_NAME ---------- --------- ---------- ---------- ---------- USER24 28-MAY-19 insert temidayo USER24 28-MAY-19 update Jennifer qswd USER24 28-MAY-19 update Michael qswd USER24 28-MAY-19 update Den qswd USER24 28-MAY-19 update Alexander qswd USER24 28-MAY-19 update Shelli qswd USER24 28-MAY-19 update Sigal qswd USER24 28-MAY-19 update Guy qswd USER24 28-MAY-19 update Karen qswd USER24 28-MAY-19 update omar qswd 10 rows selected. SQL> --write a trigger to endure that all names are entered in uppercase SQL> create or replace trigger ttt 2 before update or insert on bffs 3 for each row 4 declare 5 begin 6 :new.name := upper(:new.name); 7 end; 8 / Warning: Trigger created with compilation errors. SQL> sho err Errors for TRIGGER TTT: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/2 PLS-00049: bad bind variable 'NEW.NAME' 3/21 PLS-00049: bad bind variable 'NEW.NAME' SQL> ed Wrote file afiedt.buf 1 create or replace trigger ttt 2 before update or insert on bffs 3 for each row 4 declare 5 begin 6 :new.first_name := upper(:new.first_name); 7* end; SQL> / Trigger created. SQL> insert into bffs(first_name) 2 values('xcxvcbvnv'); 1 row created. SQL> select * from bffs; FIRST_NAME PHONE_NUMBER -------------------- -------------------- qswd 515.123.4444 qswd 515.123.5555 veli 603.123.6666 qswd 515.127.4561 qswd 515.127.4562 qswd 515.127.4563 qswd 515.127.4564 qswd 515.127.4565 qswd 515.127.4566 nazife 66778 favour 3222 temidayo 789 kareem 42069 Temidayo 0533840 ali 333 ify 12345 shahriar 17700062 XCXVCBVNV Yussuf 12345 qswd 545255655 amirhossein 4464654 21 rows selected. SQL> spool off