SQL Server trigger for update inserted row

Question

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

Table 1:

id - integer - primary key
status - integer - values: 1 for busy and 0 for available

Table 2:

id - integer - primary key - autogenerated
id2 - integer - NULL or the value of an id from table1
status - integer - values: 1 for working 0 for waiting
col1 - irrelevant column 1
col2 - irrelevant column 2
col3 - irrelevant column 3

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

You have a lot of errors in your trigger, and a logic itself. You say you can have NULL in table2 ID column but it is PK with auto generated property... — Giorgi Nakeuri
inserted and deleted are tables so that they can represent the results of set operations. Designing a trigger on the assumption that it will always process exactly one row is generally a bad plan. If you are absolutely certain there will never be more than one row then please add a check for the number of rows and use RaIsError or Throw to explicitly inform those who come along later that they have attempted to execute an unacceptable statement. (if ( select Count(*) from inserted ) > 1 RaIsError( 'FooTable_Insert: No more than one row may be processed.', 25, 42 ) with log) — HABO

gmiley gmiley · Accepted Answer · 2016-05-28T10:40:24

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.

SQL Server trigger for update inserted row

1 Answers