SQL - trigger fired when table is not updated

Hi,

First of all what microsoft tells us about triggers:

"A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected."

            Taken from: http://msdn.microsoft.com/en-us/library/ms189799.aspx

There is something interesting in above sentences, I suppose many of us would not understand correctly the last sentence: "These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected". I have also walked through these problems some days before.

Let me explain that sentence. So for example if we have a table named extremedev with coulmns: id(int, primaryKey) and test(int). We will have some data in that table:

id      test

1       1
2       2
3       0

In order to see if the trigger was actioned we will create a trigger like below:

CREATE TRIGGER test_after_inserrt_update
ON test
AFTER INSERT, UPDATE
AS
   DECLARE @nrRows INT
   SELECT @nrRows = COUNT(1) FROM inserted
   PRINT @nrRows
GO

Now, if you will do the following:

UPDATE test
SET test = 1
WHERE test = 1

And if you take a look at the messages you will see:
1
(1 row(s) affected)

Which means that your trigger was fired and everything was processed normally.
However if you do the following:

UPDATE test
SET test = 1
WHERE id = 50 -- An ID which does not exist


You will have in messages the following:
0

(0 row(s) affected)

Which means that your trigger was fired which actually means that if your trigger had a lot of logic he would do that without any sense, and you also can see that table inserted has no rows in it. Respectively if you would like to do something like an integrity check in our example, for example we would like to check if values inserted in the table test2 exists in table test, then don't do it like this:

CREATE TRIGGER test_after_inserrt_updateON test2
AFTER INSERT, UPDATE
AS
   IF NOT EXISTS(
      SELECT 1
      FROM inserted AS test2
      INNER JOIN test
        ON test.test = test2.test
    )
    BEGIN
      ROLLBACK TRAN
      RAISERROR(16, 'Integrity error, table test does not contain such value.')
    END
GO

Why not do do it like above? very simple, because if you will do an update like above then table inserted will not have any rows and that will lead to that that an error will be thrown. This is why you need to replace above with the following:

CREATE TRIGGER test_after_inserrt_updateON test2
AFTER INSERT, UPDATE
AS
   IF (SELECT TOP 1 FROM inserted) IS NOT NULL
   AND NOT EXISTS(
      SELECT 1
      FROM inserted AS test2
      INNER JOIN test
        ON test.test = test2.test
    )
    BEGIN
      ROLLBACK TRAN
      RAISERROR(16, 'Integrity error, table test does not contain such value.')
    ENDGO

So now you understand that sentence from microsoft, trigger is fired even when it is not really needed, which is a little odd from my point of view.

Let me know your opinion about this, leave a comment. Subscribe for more interesting posts.