How To Move an Updated Record into a History Table?

Question

I have the following table:

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

Its corresponding history table is

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

Every time an insert or update is occurred on FE_USER table, i need to input this newly inserted record or the updated record into the history table.

How can i write the trigger in t-sql?

Here is my pseducode, but i get alot of errors:

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 does not support CREATE OR REPLACE unfortunately. You need to use either CREATE or ALTER dependant upon what action you are doing.

Also it does not have row level triggers. All the affected rows are available to you in pseudo tables called INSERTED or DELETED

The simplest way would probably be 2 separate triggers.

For Insert

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

And for Update

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

Just following up on the approach I mentioned in the comments

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

Answer 2

If you want to capture all 3 actions I'd use 2 triggers rather than one:

CREATE TRIGGER user_to_historyA ON FE_USER
AFTER INSERT, UPDATE 
AS
DECLARE @action varchar(50)
    SET @action = CASE WHEN UPDATE(username) THEN 'UPDATE' ELSE 'INSERT' END

    INSERT INTO FE_USER_HIS(userid, username, v_action)
    SELECT userid, username, @action
    FROM inserted

GO


CREATE TRIGGER user_to_historyB ON FE_USER
AFTER DELETE
AS
    INSERT INTO FE_USER_HIS(userid, username, v_action)
    SELECT userid, username, 'DELETE'
    FROM deleted

GO

EDIT (per Martin's correct comment regarding use of the useless UPDATE() function and to consolidate into 1 trigger)

    ALTER TRIGGER user_to_history ON FE_USER
    AFTER INSERT, UPDATE, DELETE 
    AS
    DECLARE @action varchar(50)
        IF EXISTS(SELECT username FROM inserted)
        BEGIN
            SET @action = CASE WHEN EXISTS(SELECT username FROM deleted) THEN 'UPDATE' ELSE 'INSERT' END

            INSERT INTO FE_USER_HIS(userid, username, v_action)
            SELECT userid, username, @action
            FROM inserted
        END
        ELSE
            INSERT INTO FE_USER_HIS(userid, username, v_action)
            SELECT userid, username, 'DELETE'
            FROM deleted
    GO