Hi I have 2 tables name say EmpOne
and 开发者_开发百科NotificationEmp1
both table having same structure with same column name, I want to insert update record in NotificationEmp1
table using trigger when I insert update records into EmpOne
Well I fail to see the point of a trigger that keeps a copy of the table exactly in sync, and you've provided very vague specs, but I'll give it a shot.
CREATE TRIGGER dbo.tr_EmpOne
ON dbo.EmpOne
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.NotificationEmp1(columns)
SELECT columns
FROM inserted AS i
WHERE NOT EXISTS
(SELECT 1 FROM deleted WHERE key_column = i.key_column);
UPDATE n
SET col1 = i.col1,
col2 = i.col2 --, etc etc
FROM dbo.NotificationEmp1 AS n
INNER JOIN inserted AS i
ON i.key_column = n.key_column
INNER JOIN deleted AS d
ON i.key_column = d.key_column;
END
GO
Now don't forget you're going to need something to delete rows from NoticiationEmp1 when rows are deleted from EmpOne. Also there is no error handling here at all - as an example (and this won't raise an error), consider the case where a row has been deleted directly from NotificationEmp1, and is later updated in EmpOne, it will fall through the cracks here...
Within a trigger you have logical (conceptual) tables called "Inserted" and "deleted" that hold the records that have been modified. So you can just insert the records from these.
CREATE TRIGGER [dbo].[Employee_Write_Audit]
ON [dbo].[EmpOne]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- all new inserted records and updated.
Insert into NotificationEmp1 select * from Inserted
-- just the deleted ones not the updated rows that are being removed.
Insert into NotificationEmp1 select * from Deleted where EmpOneId not in (select EmpOneId from inserted)
END
精彩评论