Trigger object: run before insert/update/delete

Question

What property do I need to set on a Trigger object to have it run before the INSERT/UPDATE/DELETE operation, instead of after? I wrote the following code, deployed it and found that it had created an AFTER trigger instead of a FOR trigger. For this particular case, I need the trigger to run before the INSERT or UPDATE operation takes place. I can't seem to find the answer online.

                Trigger trg = new Trigger(parent, triggerName);
                trg.TextMode = false;
                trg.Insert = true;
                trg.Update = true;
                trg.InsertOrder = ActivationOrder.First;
                trg.UpdateOrder = ActivationOrder.First;
                trg.TextBody = lovTable.ExtendedPropertyValue("Population Query");
                trg.ImplementationType = ImplementationType.TransactSql;
                trg.Create();

Answer 1

Trigger.InsertOrder Property

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.trigger.insertorder.aspx

trigger.InsertOrder = Microsoft.SqlServer.Management.Smo.Agent.ActivationOrder.First;

Answer 2

My code already sets InsertOrder and UpdateOrder to First (see OP). However, when I have SSMS generate the code for the trigger, it contains "AFTER INSERT, UPDATE" instead of "FOR INSERT, UPDATE."

Answer 3

What property do I need to set on a Trigger object to have it run before the INSERT/UPDATE/DELETE operation, instead of after? I wrote the following code, deployed it and found that it had created an AFTER trigger instead of a FOR trigger. For this particular case, I need the trigger to run before the INSERT or UPDATE operation takes place. I can't seem to find the answer online.

Hi Jay Bienvenu,

There are two types of data manipulation language (DML) triggers:

• AFTER trigger (also as known as "FOR trigger")
• INSTEAD OF trigger

When creating a AFTER trigger:

AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully.
AFTER is the default when FOR is the only keyword specified. 

Copied from: CREATE TRIGGER (Transact-SQL) 

From your description, I think you need to create INSTEAD OF triggers that replace the standard action of the DML statements like INSERT or UPDATE. You need to specify the Trigger.InsteadOf property to create INSTEAD OF triggers using SMO, please see:

Using INSTEAD OF Triggers 

Designing INSTEAD OF Triggers

Trigger.InsteadOf Property 

Jian Kang
Forum Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact [e-mail].

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

Answer 4

I'm not trying to create "instead-of" triggers; I'm trying to create "before" triggers. I need the trigger to fire and act before the statement executes. What happens if I put the statement that I am trying to execute inside an "instead-of" trigger? Does it execute it or does it fire the trigger, producing an endless recursive loop?

Answer 5

Please see the following description:

INSTEAD OF triggers override the standard actions of the triggering statement: an INSERT, UPDATE, or DELETE. An INSTEAD OF trigger can be defined to perform error or value checking on one or more columns, and then perform additional actions before inserting the record. For example, when the value being updated in an hourly wage column in a payroll table exceeds a specified value, a trigger can be defined to either produce an error message and roll back the transaction, or insert a new record into an audit trail before inserting the record into the payroll table.

Copied from: Using INSTEAD OF Triggers

That is, INSTEAD OF triggers cause their source DML operation to skip and they just execute the code provided inside them.

Please also have a look at the following example:

CREATE TABLE Orders
(
	OrderID INT,
	ProductID INT,
	QTY INT
)
GO

CREATE TRIGGER trig_Orders ON Orders
INSTEAD OF INSERT
AS
BEGIN
	INSERT INTO Orders 
	SELECT OrderID,ProductID,QTY*2
	FROM inserted
END
GO

INSERT INTO Orders VALUES (1,1,1)

SELECT * FROM Orders

--  Result

--  OrderID     ProductID   QTY
--  ----------- ----------- -----------
--  1           1           2

DROP TABLE Orders

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

Answer 6

In addition to Jian Kang's guidance (an INSTEAD OF trigger does not endlessly recurse) there is something else you should know.

Even with an INSTEAD OF trigger, any constraints that would be violated by a planned insert / update / delete will terminate the statement before the INSTEAD OF trigger can take over.  In that case, the statement would fail completely.  

Therefore, the TSQL (statement, batch, stored procedure, script, etc.) that fires the INSTEAD OF trigger must handle any constraint issues (such as NULLs, unique indexes or constraints, etc.) before issuing the insert / update / delete that would cause the original statement to fail, so that the INSTEAD OF trigger is never called. 

RLF

Answer 7

Here is a sample instead of trigger to demonstrate the constraint issue:

USE tempdb;
GO
CREATE TABLE dbo.test
  (uniqueid INT,
   descr NVARCHAR(10) NOT NULL);
GO
CREATE UNIQUE INDEX aaa_uniqueid on dbo.test(uniqueid);
GO
CREATE TRIGGER insert_test_trig ON dbo.test 
INSTEAD OF INSERT
AS 
BEGIN
  INSERT INTO test 
     SELECT * FROM inserted;
  SELECT 'From Trigger', descr 
     FROM dbo.test;
END;
GO
INSERT INTO dbo.test VALUES (1, 'first');
GO
INSERT INTO dbo.test VALUES (1, 'second');
GO
INSERT INTO dbo.test VALUES (3, NULL);
GO
INSERT INTO dbo.test VALUES (4, 'fourth');
GO
SELECT * FROM dbo.test;
GO
DELETE dbo.test;
GO
DROP TABLE dbo.test;
GO

You will get errors from 'second' and NULL and the trigger will not fire, but it will fire for 'first' and 'fourth'.

RLF

Answer 8

Go through this link, it may help you.

http://docs.oracle.com/cd/B19306_01/server.102/b14220/triggers.htm