SQL Server trigger on Insert and Update

Question

I am looking to create a SQL Server trigger that moves a record from one table to an identical replica table if the record matches a specific condition.

Questions: do I need to specify each column, or can I use a wildcard?

Can I use something like:

SET @RecID = (SELECT [RecoID] FROM Inserted)

IF NULLIF(@RecID, '') IS NOT NULL
    (then insert....)

THANKS!

Answer 1

You could, but I'd recommend against it. If your source table changed things would start failing.

Also, in your example if you were to ever have more than one row inserted at a time you would get thrown an error (or have unpredictable results). I'd recommend a more set based approach:

   INSERT table2 (   user_id ,
              user_name ,
              RecoID
          )
   SELECT user_id ,
          user_name ,
          RecoID
   FROM   inserted i
          LEFT JOIN table2 t ON i.RecoID = t.RecoID
   WHERE  t.RecoID IS NULL;

EDIT:

If you want to stop the insert happening on your original table then you'll need to do something along the lines of:

CREATE TRIGGER trigger_name
ON table_orig
INSTEAD OF INSERT
AS
    BEGIN

    -- make sure we aren't triggering from ourselves from another trigger
    IF TRIGGER_NESTLEVEL() <= 1
        return;

        -- insert into the table_copy if the inserted row is already in table_orig (not null)
        INSERT table_copy (   user_id ,
                              user_name ,
                              RecoID
                          )
           SELECT user_id ,
                  user_name ,
                  RecoID
           FROM   inserted i
                  LEFT JOIN table_orig c ON i.RecoID = c.RecoID
           WHERE  t.RecoID IS NOT NULL;

        -- insert into table_orig if the inserted row is not already in table_orig (null)
        INSERT table_orig (   user_id ,
                              user_name ,
                              RecoID
                          )
               SELECT user_id ,
                      user_name ,
                      RecoID
               FROM   inserted i
                      LEFT JOIN table_orig c ON i.RecoID = c.RecoID
               WHERE  t.RecoID IS NULL;
    END;

The instead of will stop the insert if you don't want it to actually be inserted, so you'll need to do that yourself (the second insert statement).

Please note I changed some nulls to not nulls and the table we are left joining to in some cases.

Answer 2

There's a lot of stuff you "CAN" do in a trigger, but that doesn't mean you should. I'd would urge to to avoid setting scalar variables within a trigger at all costs. Even if you 100% sure your table will never have more that 1 row inserted per transaction because that's how the app is designed... You'll be in for very rude awakening when you find out that not all transactions come through the application.

Below is a quick demonstration of both types of triggers...

USE tempdb;
GO

IF OBJECT_ID('tempdb.dbo.PrimaryTable', 'U') IS NOT NULL 
DROP TABLE dbo.PrimaryTable;
GO 
IF OBJECT_ID('tempdb.dbo.TriggerScalarLog', 'U') IS NOT NULL 
DROP TABLE dbo.TriggerScalarLog;
GO  
IF OBJECT_ID('tempdb.dbo.TriggerMultiRowLog', 'U') IS NOT NULL 
DROP TABLE dbo.TriggerMultiRowLog;
GO

CREATE TABLE dbo.PrimaryTable (
    Pt_ID INT NOT NULL IDENTITY (1,1) PRIMARY KEY CLUSTERED,
    Col_1 INT NULL,
    Col_2 DATE NOT NULL 
        CONSTRAINT df_Col2 DEFAULT (GETDATE())
    );
GO 
CREATE TABLE dbo.TriggerScalarLog (
    Pt_ID INT,
    Col1_Old INT,
    Col1_New INT,
    Col2_Old DATE,
    Col2_New DATE
    );
GO 
CREATE TABLE dbo.TriggerMultiRowLog (
    Pt_ID INT,
    Col1_Old INT,
    Col1_New INT,
    Col2_Old DATE,
    Col2_New DATE
    );
GO 

--=======================================================

CREATE TRIGGER dbo.PrimaryCrudScalar ON dbo.PrimaryTable
AFTER INSERT, UPDATE, DELETE 
AS 
    SET NOCOUNT ON;
    DECLARE 
        @Pt_ID INT,
        @Col1_Old INT,
        @Col1_New INT,
        @Col2_Old DATE,
        @Col2_New DATE;

    SELECT 
        @Pt_ID = ISNULL(i.Pt_ID, d.Pt_ID),
        @Col1_Old = d.Col_1,
        @Col1_New = i.Col_1,
        @Col2_Old = d.Col_2,
        @Col2_New = i.Col_2
    FROM 
        Inserted i
        FULL JOIN Deleted d
            ON i.Pt_ID = d.Pt_ID;

    INSERT dbo.TriggerScalarLog (Pt_ID, Col1_Old, Col1_New, Col2_Old, Col2_New) 
    VALUES (@Pt_ID, @Col1_Old, @Col1_New, @Col2_Old, @Col2_New);
GO -- DROP TRIGGER dbo.PrimaryCrudScalar; 

CREATE TRIGGER PrimaryCrudMultiRow ON dbo.PrimaryTable
AFTER INSERT, UPDATE, DELETE 
AS 
    SET NOCOUNT ON;

    INSERT dbo.TriggerMultiRowLog (Pt_ID, Col1_Old, Col1_New, Col2_Old, Col2_New)
    SELECT 
        ISNULL(i.Pt_ID, d.Pt_ID),
        d.Col_1,
        i.Col_1,
        d.Col_2,
        i.Col_2
    FROM 
        Inserted i
        FULL JOIN Deleted d
            ON i.Pt_ID = d.Pt_ID;
GO -- DROP TRIGGER dbo.TriggerMultiRowLog;

--=======================================================
--=======================================================

-- --insert test...
INSERT dbo.PrimaryTable (Col_1)
SELECT TOP 100
    o.object_id
FROM
    sys.objects o;

SELECT 'INSERT Scarar results';
SELECT * FROM dbo.TriggerScalarLog tsl;
SELECT 'INSERT Multi-Row results';
SELECT * FROM dbo.TriggerMultiRowLog tmrl;

UPDATE pt SET 
    pt.Col_1 = pt.Col_1 + rv.RandomVal,
    pt.Col_2 = DATEADD(DAY, rv.RandomVal, pt.Col_2)
FROM
    dbo.PrimaryTable pt
    CROSS APPLY ( VALUES (ABS(CHECKSUM(NEWID())) % 10000 + 1) ) rv (RandomVal);

SELECT 'UPDATE Scarar results';
SELECT * FROM dbo.TriggerScalarLog tsl;
SELECT 'UPDATE Multi-Row results';
SELECT * FROM dbo.TriggerMultiRowLog tmrl;

DELETE pt
FROM
    dbo.PrimaryTable pt;

SELECT 'DELETE Scarar results';
SELECT * FROM dbo.TriggerScalarLog tsl;
SELECT 'DELETE Multi-Row results';
SELECT * FROM dbo.TriggerMultiRowLog tmrl;