Row level, statement level Trigger

February 24, 2020Posted inORACLE

Create two tables based on the query:

Copy Code
CREATE TABLE product
(
   MESSAGE        VARCHAR2 (50),
   CURRENT_DATE   DATE
);


CREATE TABLE product_log
(
   MESSAGE        VARCHAR2 (4000),
   CURRENT_DATE   DATE
);
  • Statement level trigger
Copy Code
CREATE OR REPLACE TRIGGER before_update_stat_product
   BEFORE UPDATE
   ON product
BEGIN
   INSERT INTO product_log
        VALUES ('Before update, statement level', SYSDATE);
END;
/

CREATE OR REPLACE TRIGGER after_update_stat_product
   AFTER UPDATE
   ON product
BEGIN
   INSERT INTO product_log
        VALUES ('After update, statement level', SYSDATE);
END;
/
  • Row level trigger
Copy Code
CREATE OR REPLACE TRIGGER before_update_row_product
   BEFORE UPDATE
   ON product
   FOR EACH ROW
BEGIN
   INSERT INTO product_log
           VALUES (
                        'Before update row level :   '
                     || :old.MESSAGE
                     || ' - - '
                     || :new.MESSAGE,
                     SYSDATE);
END;
/

CREATE OR REPLACE TRIGGER after_update_row_product
   AFTER INSERT
   ON product
   FOR EACH ROW
BEGIN
   INSERT INTO product_log
        VALUES ('After insert, Row level', SYSDATE);
END;
/
  • Test the trigger:
insert into product values ('ashish',sysdate);
/
update  product set message = 'Ashish Sahay' where current_date = sysdate;
/
Tags: