--------------------------------------------------------------------------------
-- Triggers with conditional Predicates

-- When you create a trigger for more than one DML operation, you can use conditional predicates within the trigger body to execute specific blocks of code, depending on the type of statement that fires the trigger. Conditional predicates are evaluated as follows:

-- INSERTING  
-- returns true if the trigger fires for an INSERT statement.  

-- DELETING  
-- returns true if the trigger fires for a DELETE statement.  

-- UPDATING  
-- returns true if the trigger fires for an UPDATE statement.  

-- UPDATING (column_name)  
-- returns true if the trigger fires for an UPDATE statement and column_name is updated.
--------------------------------------------------------------------------------


-- Example


-- We have two tables: classified_table and audit_table
CREATE TABLE classified_table
(
  key VARCHAR2(16),
  formula VARCHAR2(16)
);
CREATE TABLE audit_table
(
  log VARCHAR2(64),
  timestamp DATE DEFAULT SYSDATE
);



-- Whenever we do some operations modifing classified_table (i.e. insert, update, delete), we want to record it in audit_table.
-- We use conditional predicates to provide information about which DML statement fires trigger AUDIT_TRIGGER as following:

CREATE OR REPLACE TRIGGER audit_trigger
BEFORE INSERT OR DELETE OR UPDATE ON classified_table 
FOR EACH ROW
BEGIN
  IF INSERTING
  THEN INSERT INTO audit_table(log)
         VALUES (USER || ' is inserting, new key=' || :new.key);
  ELSIF DELETING
  THEN INSERT INTO audit_table(log)
         VALUES (USER || ' is deleting, old key=' || :old.key);
  ELSIF UPDATING('formula')
  THEN INSERT INTO audit_table(log)
         VALUES (USER || ' is updating, old formula=' || :old.formula
                 || ' new formula=' || :new.formula);
  ELSIF UPDATING
  THEN INSERT INTO audit_table(log)
         VALUES (USER || ' is updating, old key=' || :old.key
                 || ', new key=' || :new.key);
  END IF;
END;


-- We do some operations:

INSERT INTO classified_table VALUES('square', 'f(x)=x^2');

-- wait 2 seconds
INSERT INTO classified_table VALUES('cube', 'f(x)=x^4');

-- wait 2 seconds
INSERT INTO classified_table VALUES('sin', 'f(x)=log x');

-- wait 2 seconds
UPDATE classified_table SET formula='f(x)=x^3' WHERE key='cube';

-- wait 2 seconds
UPDATE classified_table SET key='log' WHERE key='sin';

-- wait 2 seconds
DELETE FROM classified_table WHERE key='square';


-- check the results
SELECT log, TO_CHAR(timestamp, 'IYYY/MON/DD HH24:MI:SS') FROM audit_table;


-- Please try!