I have a trigger that is called when there's an Update, Insert or Delete on a table that is joined to another table by a PK/FK 1 to 1 relationship.
Currently I'm copying rowX from TableA when a U, I or D occurs. I want it to also copy rowX from TableB at the same time.
How to I do this?
USE [Database] GO /****** Object: Trigger [dbo].[archiveTable] Script Date: 14/01/2014 3:48:08 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[archiveTable] ON [dbo].[TableA] AFTER INSERT,UPDATE,DELETE AS DECLARE @HistoryType char(1) --"I"=insert, "U"=update, "D"=delete DECLARE @Id INT SET @HistoryType=NULL SET @Id=NULL IF EXISTS (SELECT * FROM INSERTED) BEGIN IF EXISTS (SELECT * FROM DELETED) BEGIN --UPDATE SET @HistoryType='U' END ELSE BEGIN --INSERT SET @HistoryType='I' END --handle insert or update data INSERT INTO [database2].[dbo].[tableA] (column1, column2, ...) SET @Id=INSERTED.column5 SELECT GETDATE(), @HistoryType, column1, column2, ... FROM INSERTED JOIN tableB ON INSERTED.column5 = table5.column1 INSERT INTO [database2].[dbo].[tableB] (column1, column2, column3, ...) SELECT GETDATE(), @HistoryType, column1, column2, column3, .... FROM jobdtl WHERE column1 = INSERTED.column5 END ELSE IF EXISTS(SELECT * FROM DELETED) BEGIN --DELETE SET @HistoryType='D' same as above except for delete END
I'm guessing I need an inner join somewhere or do I need a variable to get the @job_id so it knows to copy the relevant info from the second table?
edit - from the looks of it I need to somehow use SCOPE_IDENTITY() however it's not taking the job_id of the transaction it's taking the actual ID of the transaction (ie. 1, 2, 3, etc. whereas I need it to be dynamic as job_id may be 54, 634, 325, etc.)
To run multiple statements in a trigger, enclose the statements in a BEGIN END block.
You may define multiple triggers for a particular event.
To refer to inserted/updated data, use "inserted" pseudo table. For deleted rows, use "deleted" pseudo table.
It's not clear from your question whether you want to insert into TableA/TableB or if these are the tables for which the triggers are defined. (The SELECT statement lacks a from clause)