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

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

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

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:

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:

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:

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:

• SQL Server AFTER INSERT and INSTEAD OF INSERT Trigger Performance
• Oracle to SQL Server Migration Knowledge Base

Oracle 11g R2 SQL Language Reference

• CREATE TRIGGER Statement

Microsoft SQL Server 2012 - Books Online

• CREATE TRIGGER Statement

Dmitry Tolpeko, dmtolpeko@sqlines.com - June 2012