CODEWITHSUNDEEP
sql-servert-sqltriggerssql-server-2012sql-update
Awesome
Awesome

Reputation: 47

SQL Server trigger for update inserted row

I want to create a trigger for this: I have two tables:

Table 1:

Table 2:

and when the users does this

INSERT INTO table2 (col1, col2, col3)
VALUES (val1, val2, val3);

the trigger should check table1 and if it finds and available id (status = 0) it updates the inserted row table2.id2 = table1.id and table2.status becomes 1 otherwise table2.id2 remains NULL and table2.status becomes 0.

Now I've tried it many ways and I always get error messages.

This is my latest attempt:

CREATE TRIGGER myTrigger 
ON table2
AFTER INSERT 
AS
BEGIN
    IF NOT EXISTS (SELECT TOP 1 id 
                   FROM table1
                   WHERE status = 0)
    BEGIN
        UPDATE table2
        SET status = 0
        WHERE table2.id = Inserted.id
    END
    ELSE 
    BEGIN
        SET status = 1
        WHERE table2.id = Inserted.id

        SET table.id@ = (SELECT TOP 1 id 
                         FROM table1
                         WHERE status = 0)
        WHERE table2.id = Inserted.id
    END
END

and errors:

Msg 102, Level 15, State 1, Procedure myTrigger, Line 17
Incorrect syntax near '='.

Line 17: set status = 1

Msg 156, Level 15, State 1, Procedure myTrigger, Line 22
Incorrect syntax near the keyword 'where'.

Line 22: where table2.id = Inserted.id

And if you feel generous the next thing I need to do is create a trigger that when a column is deleted from table2 it sets the status of the corresponding id in table1 to 0 (available)

I am using SQL Server 2012

Upvotes: 0

Views: 4404

Answers (1)

gmiley
gmiley

Reputation: 6604

Your second statement is missing the update command:

    set status=1
    where table2.id= Inserted.id
    set table.id@ = (Select TOP 1 id 
                from table1
                where status=0)
    where table2.id= Inserted.id

Should be

    update table2 set table2.status=1,
           table2.id = (Select TOP 1 id 
                from table1
                where status=0)
    join Inserted as i
    on table2.id= Inserted.id;

This should fix your errors, however, your entire trigger could be refactored to be a bit cleaner, but that is outside of the scope of the question.

Upvotes: 4

Related Questions