pl sql trigger determine if insert or update or delete
CREATE OR REPLACE TRIGGER Audit_emp
AFTER INSERT OR UPDATE OR DELETE ON Emp_tab
FOR EACH ROW
DECLARE
Time_now DATE;
Terminal CHAR(10);
BEGIN
-- get current time, and the terminal of the user:
Time_now := SYSDATE;
Terminal := USERENV('TERMINAL');
-- record new employee primary key
IF INSERTING THEN
INSERT INTO Audit_table
VALUES (Audit_seq.NEXTVAL, User, Time_now,
Terminal, 'Emp_tab', 'INSERT', :new.Empno);
-- record primary key of the deleted row:
ELSIF DELETING THEN
INSERT INTO Audit_table
VALUES (Audit_seq.NEXTVAL, User, Time_now,
Terminal, 'Emp_tab', 'DELETE', :old.Empno);
-- for updates, record the primary key
-- of the row being updated:
ELSE
INSERT INTO Audit_table
VALUES (audit_seq.NEXTVAL, User, Time_now,
Terminal, 'Emp_tab', 'UPDATE', :old.Empno);
-- and for SAL and DEPTNO, record old and new values:
IF UPDATING ('SAL') THEN
INSERT INTO Audit_table_values
VALUES (Audit_seq.CURRVAL, 'SAL',
:old.Sal, :new.Sal);
ELSIF UPDATING ('DEPTNO') THEN
INSERT INTO Audit_table_values
VALUES (Audit_seq.CURRVAL, 'DEPTNO',
:old.Deptno, :new.DEPTNO);
END IF;
END IF;
END;
Are there any code examples left?
New code examples in category SQL
-
SQL 2022-03-27 22:45:25 how to get mysql on ubuntu
-
SQL 2022-03-27 20:00:47 install latest mysql on debian 9
-
SQL 2022-03-27 19:45:11 sql '=' cannot be applied to date varchar(10) athena
-
SQL 2022-03-27 19:20:41 sql is not null
-
SQL 2022-03-27 19:20:39 sql group by error
-
SQL 2022-03-27 18:35:10 replace null with 0 in sql
-
SQL 2022-03-27 17:30:32 concat column value of same user in mysql
-
SQL 2022-03-27 17:05:02 sql developer sql worksheet not showing
-
SQL 2022-03-27 16:45:07 kill a pid redshift
-
SQL 2022-03-27 16:00:09 joins in sql server