How can I do a BEFORE UPDATED trigger with sql server?

Question

I'm using Sqlserver express and I can't do before updated trigger. There's a other way to do that?

Answer 1

It is true that there aren't "before triggers" in MSSQL. However, you could still track the changes that were made on the table, by using the "inserted" and "deleted" tables together. When an update causes the trigger to fire, the "inserted" table stores the new values and the "deleted" table stores the old values. Once having this info, you could relatively easy simulate the "before trigger" behaviour.

Answer 2

MSSQL does not support BEFORE triggers. The closest you have is INSTEAD OF triggers but their behavior is different to that of BEFORE triggers in MySQL.

SQL Server offers DML triggers

DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger (Insert, Update, Delete).

DML trigger statements use two special tables: the deleted and inserted tables.

Note

INSTEAD OF triggers override the standard actions of the triggering statement. Therefore, they can be used to perform error or value checking on one or more columns and perform additional actions before inserting, updating or deleting the row or rows.

From the now offline documentation

Specifies that the trigger is executed instead of the triggering SQL statement, thus overriding the actions of the triggering statements.

You can learn more about types of dml triggers here

Thus, actions on the update may not take place if the trigger is not properly written/handled. Cascading actions are also affected.

You may instead want to use a different approach to what you are trying to achieve.

Answer 3

Can't be sure if this applied to SQL Server Express, but you can still access the "before" data even if your trigger is happening AFTER the update. You need to read the data from either the deleted or inserted table that is created on the fly when the table is changed. This is essentially what @Stamen says, but I still needed to explore further to understand that (helpful!) answer.

The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table...

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table...

https://msdn.microsoft.com/en-us/library/ms191300.aspx

So you can create your trigger to read data from one of those tables, e.g.

CREATE TRIGGER <TriggerName> ON <TableName>
AFTER UPDATE
AS
  BEGIN
    INSERT INTO <HistoryTable> ( <columns...>, DateChanged )
    SELECT <columns...>, getdate()
    FROM deleted;
  END;

My example is based on the one here:

http://www.seemoredata.com/en/showthread.php?134-Example-of-BEFORE-UPDATE-trigger-in-Sql-Server-good-for-Type-2-dimension-table-updates

sql-server triggers

Answer 4

T-SQL supports only AFTER and INSTEAD OF triggers, it does not feature a BEFORE trigger, as found in some other RDBMSs.

I believe you will want to use an INSTEAD OF trigger.

Answer 5

All "normal" triggers in SQL Server are "AFTER ..." triggers. There are no "BEFORE ..." triggers.

To do something before an update, check out INSTEAD OF UPDATE Triggers.

Answer 6

To do a BEFORE UPDATE in SQL Server I use a trick. I do a false update of the record (UPDATE Table SET Field = Field), in such way I get the previous image of the record.

Answer 7

The updated or deleted values are stored in DELETED. we can get it by the below method in trigger

Full example,

CREATE TRIGGER PRODUCT_UPDATE ON PRODUCTS
FOR UPDATE 
AS
BEGIN
DECLARE @PRODUCT_NAME_OLD VARCHAR(100)
DECLARE @PRODUCT_NAME_NEW VARCHAR(100)

SELECT @PRODUCT_NAME_OLD = product_name from DELETED
SELECT @PRODUCT_NAME_NEW = product_name from INSERTED

END

Answer 8

Full example:

CREATE TRIGGER [dbo].[trig_020_Original_010_010_Gamechanger]
   ON  [dbo].[T_Original]
   AFTER UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @Old_Gamechanger int;
    DECLARE @New_Gamechanger int;

    -- Insert statements for trigger here
    SELECT @Old_Gamechanger = Gamechanger from DELETED;
    SELECT @New_Gamechanger = Gamechanger from INSERTED;

    IF @Old_Gamechanger != @New_Gamechanger

        BEGIN

            INSERT INTO [dbo].T_History(ChangeDate, Reason, Callcenter_ID, Old_Gamechanger, New_Gamechanger)
            SELECT GETDATE(), 'Time for a change', Callcenter_ID, @Old_Gamechanger, @New_Gamechanger
                FROM deleted
            ;

        END

END

Answer 9

Remember that when you use an instead trigger, it will not commit the insert unless you specifically tell it to in the trigger. Instead of really means do this instead of what you normally do, so none of the normal insert actions would happen.