New and Old Trigger Code

new and old trigger code

:new and :old are pseudo-records that let you access the new and old values of particular columns. If I have a table

CREATE TABLE foo (
  foo_id NUMBER PRIMARY KEY,
  bar    VARCHAR2(10),
  baz    VARCHAR2(10)
);

and I insert a row

INSERT INTO foo( foo_id, bar, baz ) 
  VALUES( 1, 'Bar 1', 'Baz 1' );

then in a row-level before insert trigger

:new.foo_id will be 1
:new.bar will be 'Bar 1'
:new.baz will be 'Baz 1'

while

:old.foo_id will be NULL
:old.bar will be NULL
:old.baz will be NULL

If you then update that row

UPDATE foo
   SET baz = 'Baz 2'
 WHERE foo_id = 1

then in a before update row-level trigger

:new.foo_id will be 1
:new.bar will be 'Bar 1'
:new.baz will be 'Baz 2'

while

:old.foo_id will be 1
:old.bar will be 'Bar 1'
:old.baz will be 'Baz 1'

If I then delete the row

DELETE FROM foo
 WHERE foo_id = 1

then in a before delete row-level trigger,

:new.foo_id will be NULL
:new.bar will be NULL
:new.baz will be NULL

while

:old.foo_id will be 1
:old.bar will be 'Bar 1'
:old.baz will be 'Baz 2'

PLSQL :NEW and :OLD

You normally use the terms in a trigger using :old to reference the old value and :new to reference the new value.

Here is an example from the Oracle documentation linked to above

CREATE OR REPLACE TRIGGER Print_salary_changes
  BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab
  FOR EACH ROW
WHEN (new.Empno > 0)
DECLARE
    sal_diff number;
BEGIN
    sal_diff  := :new.sal  - :old.sal;
    dbms_output.put('Old salary: ' || :old.sal);
    dbms_output.put('  New salary: ' || :new.sal);
    dbms_output.put_line('  Difference ' || sal_diff);
END;

In this example the trigger fires BEFORE DELETE OR INSERT OR UPDATE :old.sal will contain the salary prior to the trigger firing and :new.sal will contain the new value.

MYSQL Triggers New and OLD

This is the trigger to update country when there is an update on the city population. Let say, Seoul city's population is updated from 10M to 11M; thus the country KOR population should also increase by 1M. The problem on your trigger is the syntax on "FOR country", and it should be "FOR EACH ROW". That is, for each row that is updated in city table, do an update on country table. The difference between the old city population and new city population will be added in the country population. If the city reduces the population then it will also subtract from the country population.

CREATE TRIGGER `previous_pop` AFTER UPDATE ON city
    FOR EACH ROW
    BEGIN

      UPDATE country
      SET    Population = Population + (NEW.Population - OLD.Population)
      WHERE  Code = OLD.CountryCode;

    END;

Track and store old and new value using trigger in MSSQL

Your trigger makes a classic mistake: not taking into account multiple (or zero) rows in the inserted table.

Generally, you should combine INSERT UPDATE and DELETE triggers in one. But here it's a pretty simple trigger, so you can combine them by doing a full join on the inserted and deleted tables. You should join by primary key, I've assumed that is SL_NO

CREATE OR ALTER TRIGGER ToolHistory on [Tool Management]
AFTER INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON;

INSERT PINQC.dbo.Tool_Management_History
  (Old_Table_ID, Old_Type, New_Type, Old_Material, New_Material,
  Old_Material_Description, New_Material_Description, Old_Tool_Code, New_Tool_Code,
  Old_Tool_Description, New_Tool_Description, Old_Tool_Life, New_Tool_Life,
  Changed_By, Changed_At, Change_Type)
SELECT
  ISNULL(i.SL_NO, d.SL_NO),
  d.[Type],
  i.[Type],
  d.Material,
  i.Material,
  d.Material_Description,
  i.Material_Description,
  d.Tool_Code,
  i.Tool_Code,
  d.Tool_Description,
  i.Tool_Description,
  d.Tool_Life,
  i.Tool_Life,
  CURRENT_USER,
  GETDATE(),
  CASE WHEN d.SL_NO IS NULL THEN 'Inserted' WHEN d.SL_NO IS NULL THEN 'Deleted' ELSE 'Update' END
FROM inserted i
FULL JOIN deleted d ON d.SL_NO = i.SL_NO;

GO

Oracle Trigger with condition old not equal to new ending up with errors PLS-00049, PL/SQL: ORA-00933

Trigger code is wrong:

• there's no BEFORE keyword in trigger declaration
• you've already said that it will fire before inserting or updating; you don't have to put that into IF as well
• if you do put it there, then be careful: when there are ORs, you'll spoil everything if you don't enclose operators into parenthesis. That would be if (updating or inserting) and (:old.emp_name <> :new.emp_name) then ....
• pay attention to NULL values! When you're inserting, there's no OLD value
• there's no WHERE clause in INSERT INTO
• you missed to populate the ID column in the AUDIT table; I chose to use a sequence

Combination of what's being said above (especially missing BEGIN and superfluous WHERE) caused ORA-00933 (SQL command not properly ended) error.

Here's a full example:

SQL> create table employees (emp_id number, emp_name varchar2(20));

Table created.

SQL> insert into employees
  2  select 100, 'smith' from dual union all
  3  select 200, 'clark' from dual;

2 rows created.

SQL> create table employee_audits
  2    (id number, emp_id number, old_emp_name varchar2(20), new_emp_name varchar2(20));

Table created.

SQL> create sequence seq_audit;

Sequence created.

SQL>
SQL> create or replace trigger employee_audits_tr
  2    before insert or update on employees
  3    for each row
  4  begin
  5    if nvl(:old.emp_name, '-1') <> nvl(:new.emp_name, '-1')
  6    then
  7       insert into employee_audits
  8         (id, emp_id, old_emp_name, new_emp_name)
  9         values
 10         (seq_audit.nextval, :new.emp_id, :old.emp_name, :new.emp_name);
 11    end if;
 12  end;
 13  /

Trigger created.

Testing:

SQL> update employees set emp_name = 'blake' where emp_id = 100;

1 row updated.

SQL> update employees set emp_name = 'clark' where emp_id = 200;

1 row updated.

SQL> insert into employees values (300, 'mary');

1 row created.

SQL> select * From employee_audits;

        ID     EMP_ID OLD_EMP_NAME         NEW_EMP_NAME
---------- ---------- -------------------- --------------------
         1        100 smith                blake
         2        300                      mary

SQL>

[EDIT: keep only one row per employee]

In my opinion, you shouldn't do that - what kind of audit is it, if you've lost all previous modifications? Anyway, here's the trigger code:

• first it updates a row
• if EMP_ID doesn't exist, UPDATE won't do anything and SQL%ROWCOUNT will be 0
• in that case, INSERT a row

.

SQL> create or replace trigger employee_audits_tr
  2    before insert or update on employees
  3    for each row
  4  begin
  5    update employee_audits set
  6      old_emp_name = :old.emp_name,
  7      new_emp_name = :new.emp_name
  8      where emp_id = :new.emp_id ;
  9
 10    if sql%rowcount = 0 then
 11       insert into employee_audits (id, emp_id, old_emp_name, new_emp_name)
 12       values (seq_audit.nextval, :new.emp_id, :old.emp_name, :new.emp_name);
 13    end if;
 14  end;
 15  /

Trigger created.

SQL>

---

Related Topics