需要为审计跟踪创建触发器，以获得"incorrect syntax?"

Question

我有一个表Products，我需要在其上创建触发器来更新另一个表中数据的更改(新数据(Products_audit，该表具有相同列和相同数据。

我尝试过以下内容：

CREATE TRIGGER Super 
ON Products 
AFTER INSERT, UPDATE
AS
UPDATE Product_audit (Column1,Column2, Column3, ... )
SELECT (Column1,Column2,Column3,...)
FROM Products

收到以下错误：

Server: Msg 170, Level 15, State 31, Procedure Super, Line 20
Line 20: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Procedure Super, Line 24
Line 24: Incorrect syntax near ','.

我使用sql server 2000

我在触发器创建方面不是很强，你能纠正我吗？我想知道我错在哪里了？

Answer 1

我认为您的触发器需要看起来像这样：

CREATE TRIGGER Super 
ON Products 
AFTER INSERT, UPDATE
AS
UPDATE Product_audit 
SET Column1 = p.Column1
SET Column2 = p.Column2
FROM Products p

Answer 2

你好，您必须创建两个不同的触发器，一个用于更新，另一个用于插入1( 用于插入

CREATE TRIGGER Super
ON Products
AFTER INSERT
AS
BEGIN
INSERT INTO Product_audit 
(Column1,Column2,...)
SELECT Column1,Column2,...
FROM Inserted
END

2( 用于更新

 CREATE TRIGGER Super
    ON Products
    AFTER Update
    AS
    BEGIN
    DECLARE @Column1 as varchar(10)
    DECLARE @Column2 as varchar(10)
    DECLARE @ID INT
    SELECT @ID=ID,@Column1=Column1,@Column2=Column2 FROM INSERTED 

   UPDATE Product_audit  SET Column1=@Column1,Column2=@Column2
   WHERE ID=@ID
    END