CODEWITHSUNDEEP
sql-servertriggers
user1737391
user1737391

Reputation: 73

MSSQL Trigger Update using Inserted Table

I'm trying to write a trigger that will only run if the updated column is equal to a specific value.

Basicly, Only when I update column name "Status" to value "Paid" do I want the trigger to run.

ie

if (Update(Status) AND  inserted.Status = 'Paid')
begin
end

At the moment I get this error "The multi-part identifier "inserted.Status" could not be bound."

Thanks in advance!

Upvotes: 0

Views: 604

Answers (1)

aweis
aweis

Reputation: 5616

You need to split it into two steps. 1. only react if status is updated, 2. do what you want on all elements in inserted that has the status Paid

if update(status)
begin
    -- Do the stuff to the Paid elements in inserted
    select * from inserted as i where i.status = 'Paid'

end

remember that you status can also be updated from Paid to Paid, and you may not want to react on that. So you can do something like:

select * from
inserted as i
inner join deleted as d on i.id = d.id
where i.status <> d.status and i.status = 'Paid'

Now the result only contains the rows that has been updated to Paid and where it not already was equal to Paid

Upvotes: 1

Related Questions