sql server snippets

sql server snippets - trigger on entire table - v1.md

DROP table IF EXISTS [t1];
CREATE TABLE t1 (
    id int IDENTITY(1,1) NOT NULL,
    c1 int DEFAULT 0,
    c2 int DEFAULT 0,
    c3 int DEFAULT 0
);

DROP table IF EXISTS [t1_history];
CREATE TABLE t1_history (
    id int NOT NULL,
    c1 int DEFAULT 0,
    c2 int DEFAULT 0,
    c3 int DEFAULT 0,
    operation char(1) -- insert, update, deleted
);

DROP TRIGGER IF EXISTS [trg_t1];
CREATE TRIGGER trg_t1
    ON t1
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS(SELECT * FROM INSERTED)
           -- DELETE
           INSERT t1_history
  		   SELECT id,c1,c2,c3,'d'
  		   FROM deleted;

    ELSE
    BEGIN
        IF NOT EXISTS(SELECT * FROM DELETED)
            -- INSERT
            INSERT t1_history
  		    SELECT t1.id,t1.c1,t1.c2,t1.c3,'i'
  		    FROM t1 inner join inserted i on t1.id = i.id;
        ELSE
            -- UPDATE
           PRINT 'UPDATE';
           INSERT t1_history
  		   SELECT t1.id,t1.c1,t1.c2,t1.c3,'u'
  		   FROM t1 inner join deleted d on t1.id = d.id;
    END
END;

sql server snippets - trigger on entire table - v2.md

DROP table IF EXISTS [t1];
CREATE TABLE t1 (
    id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    c1 int DEFAULT 0,
    c2 int DEFAULT 0,
    c3 int DEFAULT 0
);

DROP table IF EXISTS [t1_history];
CREATE TABLE t1_history (
    id int NOT NULL,
    c1 int DEFAULT 0,
    c2 int DEFAULT 0,
    c3 int DEFAULT 0,
    op char(1), -- [i]nsert, [u]pdate, [d]eleted
    at datetime DEFAULT GETDATE(),
    status char(1) DEFAULT 'p' -- [p]ending, [h]andled
);

DROP TRIGGER IF EXISTS [trg_t1];
CREATE TRIGGER trg_t1
    ON t1
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS(SELECT * FROM INSERTED)
           -- DELETE
           INSERT t1_history
  		   SELECT id,c1,c2,c3,'d',GETDATE(),'p'
  		   FROM deleted;

    ELSE
    BEGIN
        IF NOT EXISTS(SELECT * FROM DELETED)
            -- INSERT
            INSERT t1_history
  		    SELECT t1.id,t1.c1,t1.c2,t1.c3,'i',GETDATE(),'p'
  		    FROM t1 inner join inserted i on t1.id = i.id;
        ELSE
            -- UPDATE
           PRINT 'UPDATE';
           INSERT t1_history
  		   SELECT t1.id,t1.c1,t1.c2,t1.c3,'u',GETDATE(),'p'
  		   FROM t1 inner join deleted d on t1.id = d.id;
    END
END;