开发者

How do I create Insert Update trigger on SQL Server 2008

开发者 https://www.devze.com 2023-03-18 17:05 出处:网络
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 wh

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
0

精彩评论

暂无评论...
验证码 换一张
取 消