CODEWITHSUNDEEP
sql-servertriggers
Mike
Mike

Reputation: 1011

Create Trigger to modify a row to the value on a joined table

I have a tables job_costcodes(id, cost_code_no, dept_id) and cost_codes(code_code_no, dept_id).

I am trying to make it so if job_costcodes.cost_code_no is modified, job_costcodes.dept_id is filled with the appropriate one from the cost_codes table, based on a matching code_code_no.

So referring to the tables below, if the top row in job_costcodes is changed to 10, the dept_id should change to 1212. Or 20 to 1313, etc.

enter image description here

I am not sure exactly how the syntax works... here is what I have so far. UPDATE: updated code.. i think it works now.

create trigger update_test on dbo.job_costcodes
for update, insert
as
begin
 set nocount on

 update dbo.job_costcodes
 set dept_id = (select CASE WHEN COUNT(1) > 0 THEN MIN(dbo.cost_codes.dept_id) ELSE NULL END as Expr1
                FROM inserted INNER JOIN
                     dbo.cost_codes ON dbo.cost_codes.cost_code_no = inserted.cost_code_no)
from inserted as i
inner join dbo.[job_costcodes] on dbo.[job_costcodes].id = i.id

end

Upvotes: 0

Views: 24

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

Treating your question as academic, start off by looking up the CREATE TRIGGER command in TSQL to get a solid understanding of the virtual tables inserted and deleted.

Then here is what I would do, in pseudo-cod-ish descriptive terms:

In your trigger, simply UPDATE job_costcodes and set the value of dept_id to the corresponding dept_id in cost_codes by JOINing to cost_codes and inserted in the FROM clause of the UPDATE.

There is no need to verify that the cost_code_no changed when doing this, the result will be the same, but if you feel you must do this, then look at the IF UPDATE() function in TSQL. You can then compare the value of cost_code_no in inserted vs deleted to know if it changed at all.

Upvotes: 1

Related Questions