UPDATE or INSERT in a trigger?

[iordanis]

In a SQL Server trigger for UPDATE INSERT, how can I determine which of the two is happening? The IF UPDATE() function is run in both cases.

[Hack]

That would be based on something external to the trigger.

What do you have that would fire the trigger off?

[Amahdy]

you can SELECT and use "CASE" to insert or update ... better if you have support for PL/SQL .. or any high level language for example ..

[Slope]

I'm not an expert on triggers by any means, but are you checking the "inserted" and "updated" tables in your code?

[iordanis]

When inserting records in the table OR updating a column to a certain value (eg. "TRUE") inside a record, I want to automatically insert some records into other tables as well. In any other case, I don't want to do anything.

The inserted table is used both in update and insert.

[iordanis]

That would be based on something external to the trigger.

What do you have that would fire the trigger off?

What do you mean? A trigger can't read or get passed any variable outside it. (if understand you correctly)

[Hack]

You can set up a trigger to fire off on an update, or an insert, or a delete, or any other kind of database change.

http://www.sqlteam.com/article/an-in...riggers-part-i

[Slope]

When inserting records in the table OR updating a column to a certain value (eg. "TRUE") inside a record, I want to automatically insert some records into other tables as well. In any other case, I don't want to do anything.

The inserted table is used both in update and insert.

It sounds like you need to use the INSTEAD OF clause and then do whatever you need to do to all tables you need to touch.

http://msdn2.microsoft.com/en-us/lib...4(SQL.80).aspx

What version of SQL Server? It might be helpful if you were to post some code.

[iordanis]

I just came up with this idea. If the count of records in the deleted table is >0 then an update must have been taken place, otherwise an insert. Could this be feasible (I haven't tried this yet, but I will shortly)

[Hack]

Actually, it sounds like a classic trigger situation and solution.

[Slope]

EDIT: My bad. There I go getting myself in trouble again.

Okay. I finally got curious enough to start playing with this. I see what you mean now about how the IF UPDATE() returns True for both inserts and updates.

There may be a better way to do this, but what seems to work is -
a) Check for records in the inserted table that already exist, then do the update procedures for those.
b) Check for records in the inserted table that do not exist and do the insert procedures for those.


An interesting thing about doing this is that it causes INSERTs to behave like the MERGE statement in Oracle (and SQL 2008). The trigger just handles updates for records that already exist and then inserts the rest.


Here is my test code.

CREATE TRIGGER trg_myTriggerTest
ON myTriggerTest
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    DECLARE @recs INT

    -- Look for records in the inserted table that already exist...
    SELECT @recs = COUNT(*)
    FROM myTriggerTest a
    JOIN inserted i ON i.myID = a.myID

    IF @Recs > 0 
        UPDATE myTriggerTest SET 
            myVal = i.myVal,
            myComment = 'UPDATED'
        FROM myTriggerTest a
        JOIN inserted i ON i.myID = a.myID



    -- Now look for records in the inserted table that don't exist...
    SELECT @recs = COUNT(*)
    FROM inserted i 
    LEFT JOIN myTriggerTest a ON a.myID = i.myID
    WHERE a.myID IS NULL

    IF @Recs > 0
        INSERT INTO myTriggerTest(myID, myVal, myComment)
        SELECT i.myID, i.myVal, 'INSERTED'
        FROM inserted i 
        LEFT JOIN myTriggerTest a ON a.myID = i.myID
        WHERE a.myID IS NULL

END

INSERT INTO myTriggerTest(myID, myVal)
SELECT 1, 'Test' UNION
SELECT 2, 'Test' UNION
SELECT 3, 'Test' UNION
SELECT 4, 'Test' UNION
SELECT 5, 'Test'



INSERT INTO myTriggerTest(myID, myVal)
SELECT 1, 'Test' UNION
SELECT 2, 'Test' UNION
SELECT 3, 'Test' UNION
SELECT 4, 'Test' UNION
SELECT 5, 'Test' UNION
SELECT 6, 'Test' UNION
SELECT 7, 'Test' UNION
SELECT 8, 'Test' UNION
SELECT 9, 'Test' UNION
SELECT 10, 'Test' UNION
SELECT 11, 'Test' UNION
SELECT 12, 'Test' UNION
SELECT 13, 'Test' UNION
SELECT 14, 'Test' UNION
SELECT 15, 'Test' UNION
SELECT 16, 'Test' UNION
SELECT 17, 'Test' UNION
SELECT 18, 'Test' UNION
SELECT 19, 'Test' UNION
SELECT 20, 'Test'