Oracle and SQL Server Triggers differences
ORACLE:
--------------
CREATE OR REPLACE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
-- Trigger logic here
INSERT INTO audit_table(user_name, action_date)
VALUES (USER, SYSDATE);
END;
/
Program:
-----------
CREATE OR REPLACE TRIGGER trig_emp_table
AFTER DELETE ON EMP
FOR EACH ROW
BEGIN
INSERT INTO emp_trigger_table (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (:OLD.empno, :OLD.ename, :OLD.job, :OLD.mgr, :OLD.hiredate, :OLD.sal, :OLD.comm, :OLD.deptno);
END;
/
(OR)
CREATE OR REPLACE TRIGGER trig_emp_table
AFTER DELETE ON EMP
FOR EACH ROW
BEGIN
INSERT INTO emp_trigger_table (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (:New.empno, :New.ename, :New.job, :New.mgr, :New.hiredate, :New.sal, :New.comm, :New.deptno);
END;
/
SQL SERVER:
------------------
CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT
AS
BEGIN
-- Trigger logic here
INSERT INTO audit_table(user_name, action_date)
SELECT SYSTEM_USER, GETDATE();
END;
Program:
-----------
CREATE TRIGGER trig_emp_table
ON EMP
AFTER DELETE
AS
BEGIN
INSERT INTO emp_trigger_table (empno, ename, job, mgr, hiredate, sal, comm, deptno)
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM deleted;
END;
(OR)
CREATE OR ALTER TRIGGER trig_emp_table
ON EMP
AFTER INSERT
AS
BEGIN
INSERT INTO emp_trigger_table (empno, ename, job, mgr, hiredate, sal, comm, deptno)
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM inserted;
END;
In SQL Server, `deleted` is a special, system-generated table, not a real user-defined table. It's automatically available inside DML triggers to let you access the "before" values of the rows affected by a `DELETE` or `UPDATE`.
SQL Server uses relational concepts, and internally, during a trigger execution, it creates two virtual tables:
Pseudo-table | Used in | Contains
-------------- ----------- -----------
`deleted` | `DELETE`, `UPDATE` | The old (pre-change) row values
`inserted` | `INSERT`, `UPDATE` | The new (post-change) row values
These act like temporary tables and can be queried using normal SQL syntax like:
SELECT * FROM deleted;
Or joined with actual tables if needed:
SELECT d.empno, e.ename
FROM deleted d
JOIN other_table e ON d.empno = e.empno;
Suppose this statement runs:
DELETE FROM emp WHERE empno = 7369;
Then inside your trigger, the `deleted` table will contain just one row: the one with `empno = 7369`.
In Oracle, `:OLD` is a bind variable used in row-level triggers to refer to the previous (old) values of a row before it was modified or deleted.
* `:OLD` is a record that holds the column values before the triggering DML operation (`UPDATE` or `DELETE`).
* It’s only available in row-level triggers, not statement-level ones.
* You must specify `FOR EACH ROW` in the trigger for `:OLD` to work.
Behavior by DML Type:
Trigger Type | `:OLD` Available? | `:NEW` Available? | Description
---------------- ---------------------- ---------------------- --------------
`INSERT` | NO | Yes | Only new values exist
`UPDATE` | Yes | Yes | Both old and new values exist
`DELETE` | Yes | No | Only old values exist
Example: `AFTER UPDATE` Trigger
CREATE OR REPLACE TRIGGER trg_audit_emp
AFTER UPDATE ON emp
FOR EACH ROW
BEGIN
INSERT INTO emp_audit (empno, old_sal, new_sal)
VALUES (:OLD.empno, :OLD.sal, :NEW.sal);
END;
/
Note:
-----
* :OLD.sal is the employee's salary before the update.
* :NEW.sal is the updated salary
* You cannot modify :OLD—it's read-only.
* In contrast, :NEW can be written to in `BEFORE INSERT` or `BEFORE UPDATE` triggers.
No comments:
Post a Comment