How to Do a Before Updated Trigger with SQL Server

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

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.

You can learn more about them here, and note that INSTEAD OF triggers "Specifies that the trigger is executed instead of the triggering SQL statement, thus overriding the actions of the triggering statements." 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.

SQL Server BEFORE UPDATE trigger which adds in a timestamp on a field before UPDATE execution

You need to identify the row(s) you need to update, and you do this with a join or semi-join. It's not going to get much more efficient than this, unless you simply don't perform the update at all:

CREATE TRIGGER [myschema].[my_table_update_ts_trigger] 
ON [myschema].[MY_TABLE]
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE t SET 
      FIELD_TO_UPDATE = i.FIELD_TO_UPDATE,
      UPDATE_TS = CURRENT_TIMESTAMP
    FROM myschema.MY_TABLE AS t
    INNER JOIN inserted AS i
    ON t.MY_TABLE_ID = i.MY_TABLE_ID;
END
GO

Here is the execution plan:

Since you need to match the rows in inserted to your base table, and since there may be more than one row that gets updated by any operation (triggers fire per statement in SQL Server, not per row like in some other platforms), and since this isn't a BEFORE update but an INSTEAD OF update (meaning you still have to actually perform the UPDATE that would have happened without the trigger in place), you need to have output from both tables in order to perform the update accurately. This means you need a JOIN, and you cannot use a SEMI-JOIN (e.g. EXISTS), which probably still violates your outlandish requirements anyway. If you only needed to update the timestamp, you could do this:

UPDATE t SET UPDATE_TS = CURRENT_TIMESTAMP
  FROM myschema.MY_TABLE AS t
  WHERE EXISTS (SELECT 1 FROM inserted WHERE MY_TABLE_ID = t.MY_TABLE_ID);

Unfortunately, that will not work, because FIELD_TO_UPDATE gets lost without actually pulling in the inserted pseudo-table in a proper join.

Another way is to use a CROSS APPLY, e.g.:

 UPDATE t SET 
   FIELD_TO_UPDATE = i.FIELD_TO_UPDATE,
   UPDATE_TS = CURRENT_TIMESTAMP
FROM  inserted AS i
CROSS APPLY myschema.MY_TABLE AS t
WHERE i.MY_TABLE_ID = t.MY_TABLE_ID;

It, too, is missing the nasty JOIN keyword, but it is still performing a JOIN. You can see this because the execution plans are identical:

Now, you can theoretically do this without a join, but that doesn't mean it will perform better. In fact I guarantee you beyond a shadow of a doubt that this will be less efficient, even though it does not contain a single four-letter word like JOIN:

    DECLARE @NOW             DATETIME = CURRENT_TIMESTAMP,
            @MY_TABLE_ID     INT,
            @FIELD_TO_UPDATE VARCHAR(255);

    DECLARE c CURSOR LOCAL FAST_FORWARD FOR
      SELECT MY_TABLE_ID, FIELD_TO_UPDATE FROM inserted;

    OPEN c;

    FETCH NEXT FROM c INTO @FIELD_TO_UPDATE, @MY_TABLE_ID;

    WHILE @@FETCH_STATUS = 0
    BEGIN
      UPDATE myschema.MY_TABLE SET 
          FIELD_TO_UPDATE = @FIELD_TO_UPDATE,
                UPDATE_TS = @NOW
        WHERE MY_TABLE_ID = @MY_TABLE_ID;

      FETCH NEXT FROM c INTO @FIELD_TO_UPDATE, @MY_TABLE_ID;
    END

    CLOSE c;
    DEALLOCATE c;

That said, if you think even for a second that this solution is going to be faster than the one with joins, I have some swampland in Florida to sell you. There are multiple bridges on the property, too. I'm not even going to bother showing the execution plans for this one.

Let's also compare what happens in an INSTEAD OF INSERT trigger. Here is an example, probably similar to what you had:

CREATE TRIGGER myschema.ins_my_table
ON myschema.MY_TABLE
INSTEAD OF INSERT
AS
  INSERT myschema.MY_TABLE(FIELD_TO_UPDATE, CREATE_TS)
    SELECT FIELD_TO_UPDATE, CURRENT_TIMESTAMP FROM inserted;
GO

This, too, will produce a plan that looks like two queries were executed:

It is important to note that an INSTEAD OF trigger cancels the original update, and you are responsible for issuing your own (even though the plan still shows two queries).

One final option would be to use an AFTER trigger instead of an INSTEAD OF trigger. This will allow you to update the timestamp without the JOIN, because the FIELD_TO_UPDATE has already been updated. But in this case you really will see two queries, and two queries will really be executed (it won't just look that way in the plans).

Some general comments

Since i'm going for performance increase I do not want to use any inner joins in the code used for the trigger.

This doesn't really make much sense; why do you think joins are bad for performance? Sounds like you've watched too many NoSQL videos. Please don't discard technology because you've heard it was bad or because you had a slow join once. Create the query that makes sense, optimize when it doesn't perform well, and come for help when you can't optimize. In almost all cases (there are exceptions, of course), the problem is indexing or statistics and not the fact that you used a JOIN keyword. That doesn't mean you should avoid all joins in all queries at all costs.

Log Trigger (BEFORE UPDATE) - SQL Server

Instead of this performance killer INSTEAD OF TRIGGER with cursor, you can simply have an after trigger something like this ....

CREATE TRIGGER trigger1
ON TABLE1
AFTER UPDATE
AS
BEGIN
   SET NOCOUNT ON;

   INSERT INTO TABLE_AUDIT (cod, [date], date_alt)
   SELECT cod, [date], GETDATE()
   FROM deleted 
END

How can I update the rows that are existed before the insert using Trigger ( SQL Server )?

Finally, I solved my problem ( the first question ) :

ALTER TRIGGER [dbo].[dbo.TR_SetActive] ON [dbo].[test]
after INSERT
NOT FOR REPLICATION 
AS
BEGIN

update dbo.test set status=0  WHERE Id < (SELECT MAX(Id) FROM dbo.test)


END

For the second question , I have used to get the last record as parameter:

set @PersonId = (select PersonId from inserted)

SQL Server Update Trigger, Get fields before and after updated

SQL Server triggers have access to 2 "magic" tables that contain a row for each row that was inserted, updated, or deleted in the statement that caused the trigger to execute.

To find all of the inserted rows on a INSERT statement:

select * from inserted

For all of the deleted rows on a DELETE statement:

select * from deleted

For UPDATE statements, each row updated will be present in both the inserted and deleted tables. The inserted table will hold the new value of the row after the update statement, and the deleted table will hold the old value of the row just before the update statement. Join between the two tables to get what you need:

select i.*, d.*
from inserted i
join deleted d on (i.id = d.id)

How to set trigger based on update query in SQL Server?

You're not even looking at the Inserted or Deleted pseudo tables in your code - how do you want to know what rows have been updated??

Inserted is a pseudo table that contains all updated rows - and it contains the new values after the update operation, while Deleted contains the same rows, but with the old values before the update.

You need to do something like this:

• join your Table1 to Inserted to get the rows that were updated (since you didn't show your table structure, I cannot know what your primary key on Table1 is - you need to use that to join to Inserted)
• add a join to your Table2 and pick those rows that have been updated, and limit those to the ones that have been updated in the Phone column (by comparing the Inserted.Phone value to Deleted.Phone)

Try this code:

CREATE TRIGGER UPDATED_Contact_Trigger 
ON table1 
AFTER UPDATE
AS
BEGIN
    -- update your base table
    UPDATE t1
    -- set the client_code to the employee_code from Table2
    SET client_code = t2.employee_code
    FROM dbo.Table1 t1
    -- join to "Inserted" to know what rows were updated - use the primary key
    INNER JOIN Inserted i ON t1.(primarykey) = i.(primarykey)
    -- join to "Deleted" to check if the "Phone" has been updated (between Deleted and Inserted)
    INNER JOIN Deleted d ON i.(primarykey) = d.(primarykey)
    -- join to "Table2" to be able to fetch the employee_code
    INNER JOIN dbo.Table2 t2 ON t2.mobile_no = t1.phone
    -- ensure the "Phone" has changed, between the old values (Deleted) and new values (Inserted)
    WHERE i.Phone <> d.Phone;
END;

Setting NEW.column in before update trigger sets values from previous trigger call

It is a BEFORE trigger so it occurs before the salesprofile table has the NEW data. I'm not entirely following but I'm go to say that this salesprofile.solutionscreteria #>> '{departments, 0}' is using the existing(previous) row not the update data the trigger is running on. Try it with NEW.solutionscreteria.

---

Related Topics