Comparison of Triggers in MS SQL and Oracle

Introduction

The purpose of this article is to show the differences in Oracle and MS SQL as to how triggers work. The main motivation for writing this article was that I could not find a suitable one that explains the differences from a MS SQL / Sybase developer's perspective. At this point the article is not meant to be an in-depth analysis and only covers regular DML triggers. It does not cover instead of triggers, system event triggers, or DDL triggers which all three also exist in both, Oracle and MS SQL.

Background

Oracle (as most other vendors) has a different trigger concept as MS SQL Server. MS SQL (such as Sybase) has a set based approach. Rows that are affected by a data modification (insert, update, delete) are stored in the inserted and deleted tables. A regular DML trigger in MS SQL is always executed after the statement. The before image is stored in the deleted table, the after image in the inserted table. Both can be accessed from within the trigger. It is also possible to join the inserted and deleted table and use them to update the table on which the trigger was executed.

In Oracle there are before and after triggers and a trigger can be defined to be executed per row or per statement (there are also compound triggers that can have a section for per row and per statement). Before per statement triggers are executed before the statement (insert, update, delete) is executed and only have access to the table before the modification. After per statement triggers are executed after the statement has completed (which is why they are sometimes called deferred, which is a bit misleading as deferred in SQL usually means that an execution is deferred till commit in a transaction that may consist of more than one statement) and only have access to the table after the modification.

This means an after statement trigger cannot see a before image and the before statement trigger cannot see an after image. Only per row triggers can see the before and after image at the same time but only per row. To access the before image of a row in a per row trigger, Oracle provides the OLD pseudorecord, to access the after image, the NEW pseudorecord, for the row currently being modified. OLD and NEW are therefore similar to deleted and inserted but they are on row level. Furthermore, it is not possible in Oracle to access or modify other rows of the table from within a per row trigger. If you try this you get the famous Oracle mutating table error. You can only modify the NEW pseudo row in a before per row trigger which is then written to the table.

Oracle disallows access to other rows of the table as conceptually, the per row trigger is fired during the table modification for each row when the row is in the process of being modified. So when the trigger fires for a given row, some rows are possibly already changed and some are not. In consequence, querying a table which is still in process to be modified is obviously very likely to be leading to inconsistent and wrong results which is why it is disallowed. Only the current row may be modified in a before per row trigger, or a per statement trigger has to be used.

Another property of triggers in Oracle which is also a regular source for errors is that they are called recursively if the table on which the trigger was invoked is doing another change on the same table. In MS SQL trigger recursion is possible but at least for regular triggers this is not the default behaviour and recursive trigger firing (also called nested triggers) needs to be set explicitly as a server option.

Mutating table error and recursive trigger error are related in the sense that they are invoked when the table on which the trigger has been invoked is accessed or modified by the same trigger but should not be confused with each other. The mutating table error occurs when trying to access or modify the same table in a per row trigger, the recursion error occurs when doing a modification on the same table in a per statement trigger that causes the trigger to fire recursively again.

Using the code

To illustrate the differences we will have a look at a simple database that has one table with person information stored in it.

CREATE TABLE Person(
    ID int NOT NULL,
    Name varchar(50) NOT NULL,
    PreviousName varchar(50) NULL,
    SameNameCount int NULL,
CONSTRAINT pk_Person PRIMARY KEY (ID))
GO

The PreviousName column should be updated whenever the value in the Name column is changing with the name before the change. The SameNameCount column should be updated whenever a row is inserted, updated, deleted and should store the number of rows in which the name column has the same value. (e.g. if there are two rows for which the Name value is "John", the SameNameCount column should have the value 2 for both rows.

MS SQL solution

In MS SQL the trigger would look like this:

CREATE TRIGGER tiud_PERSON ON Person FOR INSERT, UPDATE, DELETE
AS
BEGIN
    IF UPDATE(Name)
    BEGIN
        PRINT 'tiud_PERSON UPDATE PreviousName on Person from deleted.'
        UPDATE Person
        SET PreviousName = d.Name
        FROM PERSON p
        JOIN deleted d ON d.ID = p.ID
        WHERE d.Name <> p.Name
    END

    PRINT 'tiud_PERSON UPDATE SameName on Person from inserted and deleted.'
    UPDATE Person
    SET SameNameCount = (SELECT COUNT(*) FROM Person where Name = p.Name)
    FROM PERSON p
    WHERE Name in (SELECT Name FROM inserted UNION SELECT Name FROM deleted)
END
GO

Run the below statements to test the trigger:

INSERT Person (ID, Name) VALUES (1, 'Peter')
INSERT Person (ID, Name) VALUES (2, 'Peter')
INSERT Person (ID, Name) VALUES (3, 'Paul')
GO
 
SELECT * FROM Person
GO
 
ID          Name                                               PreviousName                                       SameNameCount
----------- -------------------------------------------------- -------------------------------------------------- -------------
1           Peter                                              NULL                                               2
2           Peter                                              NULL                                               2
3           Paul                                               NULL                                               1

(3 row(s) affected)

UPDATE Person
SET Name = 'Paul'
WHERE ID = 1
GO
 
SELECT * FROM Person
GO
 
ID          Name                                               PreviousName                                       SameNameCount
----------- -------------------------------------------------- -------------------------------------------------- -------------
1           Paul                                               Peter                                              2
2           Peter                                              NULL                                               1
3           Paul                                               NULL                                               2

(3 row(s) affected)

DELETE Person
WHERE ID = 3
GO
 
SELECT * FROM Person
GO
 
ID          Name                                               PreviousName                                       SameNameCount
----------- -------------------------------------------------- -------------------------------------------------- -------------
1           Paul                                               Peter                                              1
2           Peter                                              NULL                                               1

(2 row(s) affected)

To do the same in Oracle, a possible solution would be to have a per row trigger to update the PreviousName column and a per statement trigger to update the SameNameCount column. Before implementing this solution we first look at some implementations that cause the mutating table and recursive trigger error.

Mutating table and recursive trigger error

To create the Person table run the SQL below on Oracle:

CREATE TABLE Person(
    ID int NOT NULL,
    Name varchar(50) NOT NULL,
    PreviousName varchar(50) NULL,
    SameNameCount int NULL,
CONSTRAINT pk_Person PRIMARY KEY (ID));

To demonstrate the mutating table error, we first implement the update the SameNameCount column as a per row trigger.

CREATE OR REPLACE TRIGGER tar_Person
  AFTER INSERT OR UPDATE OR DELETE
  ON Person
FOR EACH ROW
BEGIN
    --Update SameNameCount
    UPDATE Person p
    SET SameNameCount = (SELECT COUNT(*) FROM Person WHERE Name = p.Name);
END;
/

To test the trigger and get the mutating table error, run the statement below:

INSERT INTO Person (ID, Name) VALUES (1, 'Peter');
 
ERROR at line 1:
ORA-04091: table SYSTEM.PERSON is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.TAR_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAR_PERSON'

The next implementation is the update of the SameName column as a per statement trigger to demonstrate the trigger recursion error:

--Drop previous trigger
DROP TRIGGER tar_Person;
 
CREATE OR REPLACE TRIGGER tas_Person
  AFTER INSERT OR UPDATE OR DELETE
  ON Person
BEGIN
    --Update SameNameCount
    UPDATE Person p
    SET SameNameCount = (SELECT COUNT(*) FROM Person WHERE Name = p.Name);
END;
/

Run the below statements to test the trigger and get the trigger recursion error:

INSERT INTO Person (ID, Name) VALUES (1, 'Peter');
 
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERS

Oracle solution

To get around this we need a more sophisticated approach which in this case is to split the trigger into two and let the update trigger only fire for name updates:

--Drop previous trigger
DROP TRIGGER tas_Person;
 
--Split trigger into insert, delete and update trigger
CREATE OR REPLACE TRIGGER tas_Person_id
  AFTER INSERT OR DELETE
  ON Person
BEGIN
    --Update SameNameCount
    UPDATE Person p
    SET SameNameCount = (SELECT COUNT(*) FROM Person WHERE Name = p.Name);
END;
/
 
CREATE OR REPLACE TRIGGER tas_Person_u
  AFTER UPDATE OF Name ON Person 
BEGIN
    --Update SameNameCount
    UPDATE Person p
    SET SameNameCount = (SELECT COUNT(*) FROM Person WHERE Name = p.Name);
END;
/

To test the implementation run the statements below:

--Do an insert
INSERT INTO Person (ID, Name) VALUES (1, 'Peter');
 
1 row created.
 
--Check result
SELECT * FROM Person;
 
        ID NAME
---------- --------------------------------------------------
PREVIOUSNAME                                       SAMENAMECOUNT
-------------------------------------------------- -------------
         1 Peter
                                                               1
--Now insert some more rows and check the result
INSERT INTO Person (ID, Name) VALUES (2, 'Peter');
INSERT INTO Person (ID, Name) VALUES (3, 'Paul');
 
SELECT * FROM Person;
 
        ID NAME
---------- --------------------------------------------------
PREVIOUSNAME                                       SAMENAMECOUNT
-------------------------------------------------- -------------
         1 Peter
                                                               2
 
         2 Peter
                                                               2
 
         3 Paul
                                                               1

Now that the update of the SameNameCount column works we can now create the trigger to update the PreviousName column. As a MS SQL developer that is used to after MS SQL sytle triggers you would probably first write the per row trigger as an after trigger which will however also lead to a mutating table error. The correct way to implement this is to use a before trigger:

CREATE OR REPLACE TRIGGER tbr_Person_u
  BEFORE UPDATE
  ON Person
FOR EACH ROW WHEN (NEW.Name IS NOT NULL AND NEW.Name <> OLD.Name)
BEGIN
    --Update PreviousName if name change
    :NEW.PreviousName := :OLD.Name;
END;
/
UPDATE Person SET Name = 'John' WHERE ID = 2;
 
SELECT * FROM Person;
 
        ID NAME
---------- --------------------------------------------------
PREVIOUSNAME                                       SAMENAMECOUNT
-------------------------------------------------- -------------
         1 Peter
                                                               1
 
         2 John
Peter                                                          1
 
         3 Paul
                                                               1

Limitations of MS SQL Triggers

So far Oracle triggers might have appeared as more complex and more difficult to handle as it has per statement and per row triggers that have to be used in a combination to achieve the same MS SQL is doing in a single trigger. However this separation makes Oracle triggers more powerful when it comes to operations affecting the primary key of the table.

As shown below, it is not possible for a MS SQL trigger to bring the before and after image together as the primary key which is used to join the deleted table has changed during the update: 

--Recreate records
DELETE Person
GO
INSERT Person (ID, Name) VALUES (1, 'Peter')
INSERT Person (ID, Name) VALUES (2, 'Peter')
INSERT Person (ID, Name) VALUES (3, 'Paul')
GO

--Show current rows in table
SELECT * FROM Person
GO

ID          Name                             PreviousName                     SameNameCount
----------- -------------------------------- -------------------------------- -------------
1           Peter                                  NULL                       2
2           Peter                                  NULL                       2
3           Paul                                   NULL                       1

(3 row(s) affected)

--Now do an update that also affects the primary key
UPDATE Person
SET ID = 100 + ID, Name = 'John'
GO

--Display result
SELECT * FROM Person
GO

ID          Name                             PreviousName                         SameNameCount
----------- -------------------------------- ------------------------------------ -------------
101         John                             NULL                                 3
102         John                             NULL                                 3
103         John                             NULL                                 3

(3 row(s) affected)

Whether or not updating a primary key is a sensible thing to do is a different discussion. The purpose of the above example is only to demonstrate the limitations of triggers in MS SQL and as can be seen the previous name column has not been updated while the same name column has.

As Oracle also offers a per row view the update also captures the update of the primary key and does the job as expected:  

--Recreate records
DELETE Person;

INSERT INTO Person (ID, Name) VALUES (1, 'Peter');
INSERT INTO Person (ID, Name) VALUES (2, 'Peter');
INSERT INTO Person (ID, Name) VALUES (3, 'Paul');

--Show current rows in table
SELECT * FROM Person;

        ID NAME
---------- --------------------------------------------------
PREVIOUSNAME                                       SAMENAMECOUNT
-------------------------------------------------- -------------
         1 Peter
                                                               2

         2 Peter
                                                               2

         3 Paul
                                                               1

--Do the update
UPDATE Person
SET ID = ID + 100, Name = 'John';

--Show result
SELECT * FROM Person;

        ID NAME
---------- --------------------------------------------------
PREVIOUSNAME                                       SAMENAMECOUNT
-------------------------------------------------- -------------
       101 John
Peter                                                          3

       102 John
Peter                                                          3

       103 John
Paul                                                           3

Previous name and same name column are updated with the correct values.

Mutating table in SQLite

To finish off, below is an example in SQLite (http://www.sqlite.org/) which only knows per row triggers but does not prevent the operations that would lead to a mutating table error in Oracle.

/*
SQLlite script to demonstrate mutating table problem
All triggers are per row
*/
 
drop table if exists PERSON;
 
create table PERSON (
    ID int not null PRIMARY KEY,
    Name varchar(100) not null,
    PreviousName varchar(100) null,
    SameNameCount int null);
 
create trigger tua_PERSON after update on PERSON
begin
    update PERSON
    set PreviousName = OLD.Name,
        SameNameCount = (select count(*) from PERSON where Name = NEW.Name)
    where NEW.Name <> OLD.Name
    and ID = NEW.ID and ID = OLD.ID;
end;
 
insert into PERSON (ID, Name) values (1, 'Peter'); 
insert into PERSON (ID, Name) values (2, 'Peter'); 
insert into PERSON (ID, Name) values (3, 'Peter'); 
 
select * from PERSON;
 
1|Peter||
2|Peter||
3|Peter||
 
update PERSON
set Name = 'Paul';
 
select * from PERSON;
 
1|Paul|Peter|1
2|Paul|Peter|2
3|Paul|Peter|3

The value SameNameCount column is reflecting the state of the table at the point in time in which the per row trigger was fired and thus is not consistent across the three table records.

Conclusion

The lengthiness of this article already shows the complexity of this subject and as said in the introduction this article only covers regular DML triggers.

Without going through all aspects treated above and to keep it short, MS SQL triggers are easier to use and have less pitfalls, but have limitations that Oracle triggers do not have.

History

• 29 July 2013: Initial version.