Menu
I am new to triggers and want to create a trigger on an update of a column and update another table with that value.
I have table1 with a year column and if the application updates that year column I need to update table 2 with the year the same year.
ALTER TRIGGER [dbo].[trig_UpdateAnnualYear] ON [dbo].[table1] AFTER UPDATE AS if (UPDATE (intAnnualYear)) BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here Update table2 set AnnualYear = intAnnualYear where table2.ID = table1.ID END 
205
To create a trigger, we need to change the delimiter. Inserting the row into Table1 activates the trigger and inserts the records into Table2. To insert record in Table1. To check if the records are inserted in both tables or not.
AFTER UPDATE Trigger in SQL is a stored procedure on a database table that gets invoked or triggered automatically after an UPDATE operation gets successfully executed on the specified table. For uninitiated, the UPDATE statement is used to modify data in existing rows of a data table.
In SQL Server, you can create DML triggers that execute code only when a specific column is updated. The trigger still fires, but you can test whether or not a specific column was updated, and then run code only if that column was updated. You can do this by using the UPDATE() function inside your trigger.
During the execution of an INSERT or UPDATE statement, the new or changed rows in the trigger table are copied to the inserted table. The rows in the inserted table are copies of the new or updated rows in the trigger table. An update transaction is similar to a delete operation followed by an insert operation.
You don't reference table1 inside the trigger. Use the inserted pseudo table to get the "after" values. Also remember that an update can affect multiple rows.
So replace your current update statement with
UPDATE table2 SET table2.annualyear = inserted.intannualyear FROM table2 JOIN inserted ON table2.id = inserted.id You only need to update the records in table2 if the column intannualyear is involved. Also, this is an alternative UPDATE syntax across two tables from what Martin has shown
IF UPDATE(intannualyear) UPDATE table2 SET annualyear = inserted.intannualyear FROM inserted WHERE table2.id = inserted.id If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
