February 14, 2013 at 7:29 am
Hi
I have 4 tables. When a column in Table A is updated (closed_ind), I want to update the closed_ind columns in tables B and subsequently tables C and D.
I have created a simple trigger which is fired when Table A is updated - it subsequently updates Table B perfectly. I then created a 2nd trigger on Table B which updates Table C and D.
My problem is this - both triggers work fine when I manually update the 2 tables. However the 2nd trigger does not fire when the 1st trigger updates table B...?
Apologies if that is hard to follow...
February 14, 2013 at 7:37 am
do you have nested triggers config option enabled
whats the output of
select * from sys.configurations where name = 'nested triggers'
But why not just update C and D from within the same trigger that updates B?
February 14, 2013 at 7:45 am
Hi,
I've literally got it working actually.
I couldn't simply update Tables C and D from the 1st trigger because the ID isnt the same in those tables. We did think of a way to do it using Stored Procedures and a trigger but decided it was more complicated.
We also need a 2nd trigger anyway as Table B maybe updated outside of Table A - if that is the case Table C and D must be updated as well - it's a hierarchical thing - Records in Table C and D belong to a record in Table B but records in Table B belong to a record in Table A.
Anyway thanks for your quick advice. I have checked and we do have nested triggers enabled.
February 14, 2013 at 7:50 am
Please share the steps you did to get it working, in case someone later down the line has the same issue.
February 14, 2013 at 8:01 am
Ok - this is how it works...
The column "Closed_Ind" on Table_A is updated (1 or 0). The trigger updates table_B's closed_ind column depending on whether the Table_A was a 1 or 0.
CREATE TRIGGER Table_A_Closures_cascade
ON Table_A
AFTER INSERT, UPDATE
AS
DECLARE @ID varchar(10)
IF (SELECT closed_ind FROM inserted) = 1
BEGIN
SET @ID = (SELECT id FROM inserted)
UPDATE table_B
SET closed_ind = 1
WHERE ID = @ID
END
ELSE
BEGIN
SET @BPID = (SELECT id FROM inserted)
UPDATE Table_B
SET closed_ind = 0
WHERE ID = @BPID
END
The update in the above trigger fires the following trigger against Table_B - this time updating Table C and D's column
CREATE TRIGGER Table_B_Closure_Cascade
ON TABLE_B
AFTER INSERT, UPDATE
AS
DECLARE @BID varchar(10)
IF (SELECT closed_ind FROM inserted) = 1
BEGIN
SET @BID = (SELECT BID FROM inserted)
UPDATE TABLE_C
SET Closed_ind = 1
WHERE bid = @BID
UPDATE TABLE_D
SET Closed_ind = 1
WHERE bid = @BID
END
ELSE
BEGIN
SET @BID = (SELECT BID FROM inserted)
UPDATE TABLE_C
SET Closed_ind = 0
WHERE bid = @BID
UPDATE TABLE_D
SET Closed_ind = 0
WHERE bid = @BID
END
I format my SQL in the above way to make it easier to read. I have changed the table_names/columns/trigger_names to hopefully make more sense to someone reading it. Feel free to criticise the method and suggest a better one...
I have a feeling I prematurely posted this thread. I apologies for that. Hopefully the above will help a beginner if they get stuck. Thanks for your help Anthony.
February 14, 2013 at 8:27 am
Your triggers assume there is ever only 1 row being updated. You need to change the logic here to be set based using the inserted and deleted tables.
Something like this:
CREATE TRIGGER Table_A_Closures_cascade
ON Table_A
AFTER INSERT, UPDATE
AS
update table_b
set closed_ind = case i.closed_ind when 1 then 1 else 0 end
from table_B b
join inserted i on b.ID = i.id
The way you coded them if there is more than 1 row updated or inserted it isn't going to work because triggers are not row based, they are event based and the event is an update or delete (and these can contain multiple rows).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 14, 2013 at 8:35 am
I will look at it, it will certainly make the solution more comprehensive.
The triggers I created update the tables perfectly considering they will only be executed by users selecting/deselecting a tick box via a web-app. The only way more than 1 property will be updated in the same process is if myself/my colleague executes SQL ourselves.
February 14, 2013 at 8:43 am
learning_sql (2/14/2013)
I will look at it, it will certainly make the solution more comprehensive.The triggers I created update the tables perfectly considering they will only be executed by users selecting/deselecting a tick box via a web-app. The only way more than 1 property will be updated in the same process is if myself/my colleague executes SQL ourselves.
Today that is true. In the future it is likely that will not be the case. Protect yourself now. There are stories around here about companies that have literally had to close their doors because they used triggers like this. It is easy to code it to handle set based operations.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 14, 2013 at 8:50 am
Many thanks - I have changed my code to join the inserted records...
My nick is fairly accurate - I am still learning sql..
EDIT - I have actually changed the code in both the triggers to handle the update/insert of multiple records.
This is the code:
CREATE TRIGGER Table_A_Closures_cascade
ON Table_A
AFTER INSERT, UPDATE
AS
UPDATE TABLE_B
SET closed_ind =
CASE i.closed_ind
WHEN 1
THEN 1
ELSE 0
END
FROM TABLE_B JOIN inserted i
ON TABLE_B.ID = i.ID
CREATE TRIGGER Table_B_Closures_cascade
ON Table_B
AFTER INSERT, UPDATE
AS
UPDATE TABLE_C
SET closed_ind =
CASE i.closed_ind
WHEN 1
THEN 1
ELSE 0
END
FROM TABLE_C c JOIN inserted i
ON TABLE_c.PR_ID = i.PR_ID
UPDATE TABLE_D
SET closed_ind =
CASE i.closed_ind
WHEN 1
THEN 1
ELSE 0
END
FROM TABLE_D d JOIN inserted i
ON d.PR_ID = i.PR_ID
February 14, 2013 at 9:22 am
Very nice. It really is easy to do and now your code is much more flexible. No worries about learning sql, it is a daily learning experience for me as well.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 14, 2013 at 11:01 am
Nope, you're not quite there yet, one other changed to make.
You should always specify the owner (schema name) on objects in a trigger. There are very few always/never rules in SQL, but this is one of them.
CREATE TRIGGER Table_B_Closures_cascade
ON dbo.Table_B
AFTER INSERT, UPDATE
AS
UPDATE dbo.TABLE_C
SET closed_ind =
CASE i.closed_ind
WHEN 1
THEN 1
ELSE 0
END
FROM dbo.TABLE_C c JOIN inserted i
ON TABLE_c.PR_ID = i.PR_ID
UPDATE dbo.TABLE_D
SET closed_ind =
CASE i.closed_ind
WHEN 1
THEN 1
ELSE 0
END
FROM dbo.TABLE_D d JOIN inserted i
ON d.PR_ID = i.PR_ID
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 14, 2013 at 11:14 am
Thanks Scott,
I don't always do it - usually do. Unfortunately i edited the code a lot before posting it onto the forum.
February 14, 2013 at 12:28 pm
ScottPletcher (2/14/2013)
Nope, you're not quite there yet, one other changed to make.You should always specify the owner (schema name) on objects in a trigger. There are very few always/never rules in SQL, but this is one of them.
Why is this different for a trigger than anywhere else? As a rule I always include the schema but have not heard that it is more important for a trigger.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 14, 2013 at 12:35 pm
Sean Lange (2/14/2013)
ScottPletcher (2/14/2013)
Nope, you're not quite there yet, one other changed to make.You should always specify the owner (schema name) on objects in a trigger. There are very few always/never rules in SQL, but this is one of them.
Why is this different for a trigger than anywhere else? As a rule I always include the schema but have not heard that it is more important for a trigger.
In a stored proc, it could theoretically be correct to leave it off, if you intend for different users to reference different tables. For example, when "Mary" references "tasks" table, she's supposed to see "Mary.tasks" and when "Bill" references "tasks", he's supposed to see "Bill.tasks".
But a trigger fires in response to actions on a specific table, with schema already resolved. So, you must insure that you refer to that specific table, and not accidentally to a different table. Even if SQL were to resolve this correctly at run time, that takes additional processing, and triggers should always be as efficient as possible.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 14, 2013 at 12:42 pm
ScottPletcher (2/14/2013)
Sean Lange (2/14/2013)
ScottPletcher (2/14/2013)
Nope, you're not quite there yet, one other changed to make.You should always specify the owner (schema name) on objects in a trigger. There are very few always/never rules in SQL, but this is one of them.
Why is this different for a trigger than anywhere else? As a rule I always include the schema but have not heard that it is more important for a trigger.
In a stored proc, it could theoretically be correct to leave it off, if you intend for different users to reference different tables. For example, when "Mary" references "tasks" table, she's supposed to see "Mary.tasks" and when "Bill" references "tasks", he's supposed to see "Bill.tasks".
But a trigger fires in response to actions on a specific table, with schema already resolved. So, you must insure that you refer to that specific table, and not accidentally to a different table. Even if SQL were to resolve this correctly at run time, that takes additional processing, and triggers should always be as efficient as possible.
Ahh that makes sense. Thanks for the explanation.
I probably don't use schemas as much as I could/should. I tend to be a dbo guy (with a few exceptions of course). This is starting to sound like another recent thread. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply