CREATE TRIGGER - Triggers - Oracle to SQL Server Migration

CREATE TRIGGER statement allows you to define a trigger in Oracle and SQL Server.

But besides syntax differences between Oracle PL/SQL and SQL Server Transact-SQL, there are significant design differences between triggers in these two databases.

Oracle Example:

  -- Trigger sets the value for 'created' column
  CREATE TRIGGER tr_cities BEFORE INSERT ON cities 
      FOR EACH ROW
  BEGIN
    :new.created := SYSDATE;
  END;
  /

SQL Server Example:

  -- Trigger sets the value for 'created' column
  CREATE TRIGGER tr_cities ON cities AFTER INSERT 
  AS
  BEGIN
    UPDATE cities
     SET created = GETDATE()
    FROM inserted 
    WHERE cities.id = inserted.id;
  END
  GO

Trigger Conversion Overview

Oracle trigger to SQL Server conversion summary:

Oracle SQL Server
Syntax CREATE [OR REPLACE] TRIGGER tr_name
{ BEFORE | AFTER | INSTEAD OF }
INSERT [OR UPDATE] [OR DELETE]
[FOR EACH ROW]
ON tab_name trigger_statements (full...)
CREATE TRIGGER tr_name
ON tab_name
{ AFTER | INSTEAD OF }
[INSERT] [,] [UPDATE] [,] [DELETE]
trigger_statements (full...)
Replace If Exists Use IF OBJECT_ID('tr_name', 'TR') IS NOT NULL
DROP TRIGGER tr_name
For Each Row Use inserted and deleted tables
Before Event Use AFTER or INSTEAD OF

Last Update: Oracle 11g R2 and Microsoft SQL Server 2012

Trigger Conversion Details

In most cases when you convert a Oracle trigger to SQL Server, you have to re-design it.

1. Oracle BEFORE INSERT and FOR EACH ROW Trigger Overriding Inserted Values

Often a trigger is just used to override inserted or updated column values:

Oracle:

  -- Define a table
  CREATE TABLE cities
  (
     id NUMBER(10) PRIMARY KEY,
     name VARCHAR2(90),
     created DATE
  ); 
 
  -- Trigger sets the value for 'created' column
  CREATE OR REPLACE TRIGGER tr_cities_insert BEFORE INSERT ON cities 
      FOR EACH ROW
  BEGIN
    :new.created := SYSDATE;
  END;
  /

Note. If you use DEFAULT SYSDATE clause in CREATE TABLE instead of the trigger in this example, then an user can explicitly insert any created date. The trigger allows to set the date and time when the row was actually inserted.

Conversion to SQL Server

Although this trigger is BEFORE trigger, you can safely use AFTER trigger to implement the same logic in SQL Server.

In a SQL Server trigger, you can use UPDATE statement and inserted table to get the inserted rows:

SQL Server:

  -- Define a table
  CREATE TABLE cities
  (
     id INT PRIMARY KEY,
     name VARCHAR(90),
     created DATETIME
  ); 
 
  -- Drop the trigger if exists
  IF OBJECT_ID('tr_cities_insert', 'TR') IS NOT NULL
	DROP TRIGGER tr_cities_insert
  GO
 
  -- Trigger sets the value for 'created' column
  CREATE TRIGGER tr_cities_insert ON cities AFTER INSERT
  AS
  BEGIN
    UPDATE cities
     SET created = GETDATE()
    FROM inserted 
    WHERE cities.id = inserted.id;
  END
  GO

For example, if you insert a row to both Oracle and SQL Server, the triggers will set the value of created column:

   -- Insert a row to Oracle or SQL Server
   INSERT INTO cities (id, name)  VALUES (1, 'Paris');

Inserted data:

id name created (Oracle) created (SQL Server)
1 Paris 2012-06-09 18:00:17 2012-06-09 18:00:17.810

2. Oracle BEFORE UPDATE and FOR EACH ROW Trigger Referencing New and Old Values

Consider an Oracle BEFORE UPDATE trigger that references the new and old values as well as contains flow of control statements (IF condition):

Oracle:

  -- Define a table
  CREATE TABLE cities
  (
     id NUMBER(10) PRIMARY KEY,
     name VARCHAR2(90),
     created DATE
  ); 
 
  -- Trigger does not allow to change 'created' value and to set 'name' to NULL
  CREATE OR REPLACE TRIGGER tr_cities_update BEFORE UPDATE ON cities 
      FOR EACH ROW
  BEGIN
    :new.created := :old.created;
 
    IF :old.name IS NOT NULL AND :new.name IS NULL
    THEN
      :new.name := 'Not defined';
    END IF;
  END;
  /

Conversion to SQL Server

This trigger is also BEFORE trigger, but again you can safely use AFTER trigger to implement the same logic in SQL Server.

But now you have to use both inserted and deleted tables in a SQL Server trigger to get old and new values:

SQL Server:

  -- Define a table
  CREATE TABLE cities
  (
     id INT PRIMARY KEY,
     name VARCHAR(90),
     created DATETIME
  ); 
 
  -- Drop the trigger if exists
  IF OBJECT_ID('tr_cities_update', 'TR') IS NOT NULL
	DROP TRIGGER tr_cities_update
  GO
 
  -- Trigger does not allow to change 'created' value and to set //name// to NULL
  CREATE TRIGGER tr_cities_update ON cities AFTER UPDATE 
  AS
  BEGIN
    UPDATE cities
      SET created = deleted.created
    FROM deleted
    WHERE cities.id = deleted.id;
 
    UPDATE cities
      SET name = 'Not defined'
    FROM inserted, deleted
    WHERE cities.id = inserted.id AND inserted.id = deleted.id AND
            deleted.name IS NOT NULL AND inserted.name IS NULL;
  END
  GO

For example, let's try to update the cities table, change created date and set name to NULL:

   -- Update a row in Oracle
   UPDATE cities 
   SET created = TO_DATE('2010-06-02', 'YYYY-MM-DD'), name = NULL
   WHERE id = 1;
 
   -- Update a row in SQL Server
   UPDATE cities 
   SET created = CONVERT(DATETIME, '2010-06-02'), name = NULL
   WHERE id = 1;

Data after the update:

id name created (Oracle) created (SQL Server)
1 Not defined 2012-06-09 18:00:17 2012-06-09 18:00:17.810

3. Oracle BEFORE INSERT and FOR EACH ROW Trigger Generating IDs

In Oracle, a sequence object (created by CREATE SEQUENCE statement) is used to generate IDs (auto-increment, identity values). But Oracle does not allow to use a sequence in a DEFAULT clause, so a trigger is often used to assign IDs:

Oracle:

  -- Create a sequence
  CREATE SEQUENCE cities_seq START WITH 1 INCREMENT BY 1; 
 
  -- Define a table
  CREATE TABLE cities
  (
     id NUMBER(10) PRIMARY KEY,
     name VARCHAR2(90)
  );
 
  -- Create a trigger to assign ID
  CREATE OR REPLACE TRIGGER cities_id_tr BEFORE INSERT ON cities
  FOR EACH ROW
  BEGIN
     IF :new.id IS NULL THEN
        SELECT cities_seq.nextval INTO :new.id FROM dual;
     END IF;
  END;
  /
 
  -- Insert a row (ID will be automatically generated)
  INSERT INTO cities (name) VALUES ('London');

Conversion to SQL Server

In SQL Server you have several options how to convert this trigger.

First, since SQL Server 2012 sequences are available in SQL Server, and they can be used in a DEFAULT clause. So the best option, if ID is assigned by default (there is IF :new.id IS NULL check in the trigger), is to use the DEFAULT clause instead of a trigger in SQL Server:

SQL Server:

  -- Create a sequence
  CREATE SEQUENCE cities_seq START WITH 1 INCREMENT BY 1; 
 
  -- Define a table
  CREATE TABLE cities
  (
     id INT PRIMARY KEY DEFAULT NEXT VALUE FOR cities_seq,
     name VARCHAR(90)
  );
 
   -- Insert a row (ID will be automatically generated)
  INSERT INTO cities (name) VALUES ('London');

Both tables in Oracle and SQL Server contain the following row after the insert:

id name
1 London

5. Oracle Multi-Action Trigger Generating IDs and Referencing New and Old Values

In Oracle, you can define a single trigger that handles INSERT, UPDATE and DELETE operations. INSERTING and UPDATING keywords allow you to define which action fired the trigger:

Oracle:

   -- Create a sequence
  CREATE SEQUENCE cities_seq START WITH 1 INCREMENT BY 1; 
 
   -- Define a table
  CREATE TABLE cities
  (
     id NUMBER(10) PRIMARY KEY,
     name VARCHAR2(90),
     created DATE
  ); 
 
  -- Create a trigger to assign ID, and override inserted and updated values
  CREATE OR REPLACE TRIGGER cities_id_tr BEFORE INSERT OR UPDATE ON cities
  FOR EACH ROW
  BEGIN
     -- Check for INSERT operation
     IF INSERTING THEN
        -- Assign ID by default
        IF :new.id IS NULL THEN
          SELECT cities_seq.nextval INTO :new.id FROM dual;
        END IF;
 
        -- Override created date
        :new.created := SYSDATE;
      END IF;
 
      -- Check for UPDATE operation
      IF UPDATING THEN
        :new.created := :old.created;
      END IF;
  END;
  /
 
  -- Insert a row (ID will be automatically assigned)
  INSERT INTO cities (name) VALUES ('London');
  -- Try to change 'created' date (trigger will set the original value back)
  UPDATE cities SET created = TO_DATE('2003-07-24', 'YYYY-MM-DD');

Conversion to SQL Server

To convert this trigger to SQL Server, you need to access both inserted and deleted tables. But as you can see the Oracle trigger generates ID, so you have to move ID generation out of the SQL Server trigger, otherwise there is no link between inserted and deleted tables:

SQL Server:

   -- Create a sequence
  CREATE SEQUENCE cities_seq START WITH 1 INCREMENT BY 1; 
 
   -- Define a table
  CREATE TABLE cities
  (
     id INT PRIMARY KEY,
     name VARCHAR(90),
     created DATETIME
  ); 
 
  -- Add DEFAULT to generate ID
  ALTER TABLE cities ADD DEFAULT NEXT VALUE FOR cities_seq FOR id;
 
  -- Drop the trigger if exists
  IF OBJECT_ID('cities_id_tr', 'TR') IS NOT NULL
	DROP TRIGGER cities_id_tr
  GO
 
  -- Create a trigger to override inserted and updated values
  CREATE TRIGGER cities_id_tr ON cities AFTER INSERT, UPDATE
  AS
  BEGIN
     -- Check for INSERT operation
     IF NOT EXISTS (SELECT * FROM deleted) BEGIN
 
        -- Override created date
        UPDATE cities
          SET created = GETDATE()
        FROM inserted
        WHERE cities.id = inserted.id;
     END
 
      -- Check for UPDATE operation
      IF EXISTS (SELECT * FROM deleted) BEGIN
        UPDATE cities
          SET created = deleted.created
        FROM inserted, deleted
        WHERE cities.id = inserted.id AND inserted.id = deleted.id;
      END
  END
  GO
 
  -- Insert a row (ID will be automatically assigned)
  INSERT INTO cities (name) VALUES ('London');
  -- Try to change 'created' date (trigger will set the original value back)
  UPDATE cities SET created = CONVERT(DATETIME, '2003-07-24');

Both tables in Oracle and SQL Server contain the following row after the insert and update:

id name created
1 London 2012-06-11 14:50:13

Resources

Author

Dmitry Tolpeko, dmtolpeko@sqlines.com - June 2012

Discussion

, May 08, 2013 1:11 am

Thanks a lot for writing the great overview for the conversion of triggers to MS SQL. Unfortunately when I tried to implement the first trigger (with the SYSDATE) MS SQL 2012 throws an error for the line WHERE cities.id = inserted.id; with the following error message: “The multi-part identifier “inserted.id” could not be bound.” Do you have an idea what the error might be? Thanks a lot in advance Oliver

You could leave a comment if you were logged in.