sql - 如何将更新的记录移动到历史表中？

Question

我有下表:

CREATE TABLE FE_USER
(
    userid int identity (321,4) CONSTRAINT userid_pk PRIMARY KEY,
    username varchar(40)
);

它对应的历史表是

CREATE TABLE FE_USER_HIST
(
    userid int,
    username varchar(40),
    v_action varchar(50)
);

每次对 FE_USER 表进行插入或更新时，我需要输入这条新插入的记录或更新的记录到历史表中。

如何在 t-sql 中编写触发器？

这是我的伪代码，但我遇到了很多错误:

CREATE OR REPLACE TRIGGER user_to_hist
    AFTER UPDATE OR DELETE
    ON FE_USER
    FOR EACH ROW
    DECLARE
        v_action varchar(50);
    BEGIN
        v_action := CASE WHEN UPDATING THEN 'UPDATE' ELSE 'DELETE' END;
        INSERT INTO FE_USER_HIS(userid, username, v_action)
        SELECT :OLD.userid, :OLD.username, v_action
        FROM .......;
END;

Answer 1

不幸的是，SQL Server 不支持 CREATE OR REPLACE。您需要使用 CREATE 或 ALTER，具体取决于您正在执行的操作。

它也没有行级触发器。所有受影响的行都可以在名为 INSERTED 或 DELETED

的伪表中使用

最简单的方法可能是 2 个单独的触发器。

用于插入

CREATE TRIGGER dbo.tr_i_FE_USER 
   ON  dbo.FE_USER 
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
INSERT INTO FE_USER_HIST
SELECT userid,username, 'inserted' AS v_action
FROM INSERTED

END

更新

CREATE TRIGGER dbo.tr_u_FE_USER 
   ON  dbo.FE_USER 
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
INSERT INTO FE_USER_HIST
SELECT userid,username, 'updated' AS v_action
FROM INSERTED /*If you wanted the previous value instead
                 you could use FROM DELETED */

END

只是跟进我在评论中提到的方法

CREATE TRIGGER dbo.tr_iud_FE_USER 
   ON  dbo.FE_USER 
   AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
    SET NOCOUNT ON;

    INSERT INTO FE_USER_HIST
    SELECT 
    ISNULL(i.UserId,d.UserId) AS UserId,

    CASE WHEN i.UserId IS NULL THEN d.UserName
         ELSE i.UserName
    END AS UserName,

    CASE WHEN i.UserId IS NULL THEN 'deleted' 
         WHEN d.UserId IS NULL THEN 'inserted' 
         ELSE 'updated'
    END  AS v_action
    from INSERTED i FULL OUTER JOIN DELETED d 
    ON i.UserId = d.USerId
END