CODEWITHSUNDEEP
sqlsql-servertriggersinsertdml
user5078178
user5078178

Reputation: 77

INSERT trigger causing trouble

I'm new to writing SQL Server triggers. I have a table called USERS and I also a another tabled called USERS_DELTA. The difference between the two is USERS_DELTA has one additional column called change_type.

Here are the table schema:

USERS table:

CREATE TABLE [dbo].[TDR_Users]
(
    [objectGUID] [varbinary](50) NOT NULL,
    [distinguishedName] [nvarchar](255) NOT NULL,
    [adForest] [nvarchar](50) NULL,
    [adDomain] [nvarchar](50) NULL,
    [accountExpires] [datetime] NULL,
    [adminCount] [int] NULL,
    [cn] [nvarchar](64) NULL,
    [company] [nvarchar](64) NULL,
    [description] [nvarchar](448) NULL,
    [displayName] [nvarchar](256) NULL,
    [division] [nvarchar](256) NULL,
    [employeeID] [nvarchar](16) NULL
)

And USERS_DELTA table:

CREATE TABLE [dbo].[TDR_Users]
(
    [objectGUID] [varbinary](50) NOT NULL,
    [distinguishedName] [nvarchar](255) NOT NULL,
    [adForest] [nvarchar](50) NULL,
    [adDomain] [nvarchar](50) NULL,
    [accountExpires] [datetime] NULL,
    [adminCount] [int] NULL,
    [cn] [nvarchar](64) NULL,
    [company] [nvarchar](64) NULL,
    [description] [nvarchar](448) NULL,
    [displayName] [nvarchar](256) NULL,
    [division] [nvarchar](256) NULL,
    [employeeID] [nvarchar](16) NULL,
    [change_Type] [nvarchar](10) NULL
)

I have an application which will be creating records in USERS table. But what I'm trying to do is capture the inserts into the USERS_DELTA. I have written a trigger on the USERS table:

CREATE TRIGGER [dbo].[TR_INSERTS_DELTAS]
   ON  [dbo].[Users]
   FOR INSERT
AS 
    DECLARE @ObjectGUID varbinary(50), @DN varchar(255), @memcount int;
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Get the primary and unique keys from the inserted rows.
    SELECT @DN=i.distinguishedName  FROM inserted i;
    SELECT @ObjectGUID = i.objectGUID FROM inserted i;  

    -- Check if a row already exists in the TDR_Users_Delta table with those values.
    SELECT @memcount=COUNT(*) FROM Users 
    WHERE Users.distinguishedName = @DN 
    AND Users.objectGUID = @ObjectGUID ;
    if(@memcount = 0)
    BEGIN
        INSERT INTO [dbo].[Users_Delta]
        (
            [objectGUID],
            [distinguishedName],
            [adForest],
            [adDomain],
            [accountExpires],
            [adminCount],
            [cn] ,
            [company],
            [description],
            [displayName],
            [division],
            [employeeID],
            [change_type]
        )

        VALUES
        (
            INSERTED.[objectGUID],
            INSERTED.[distinguishedName],
            INSERTED.[adForest],
            INSERTED.[adDomain],
            INSERTED.[accountExpires],
            INSERTED.[adminCount],
            INSERTED.[cn] ,
            INSERTED.[company],
            INSERTED.[description],
            INSERTED.[displayName],
            INSERTED.[division],
            INSERTED.[employeeID],
            'Add'           
        );

    END
END
GO

When I execute this trigger, I get the following error:

Msg 4104, Level 16, State 1, Procedure TR_INSERTS_DELTAS, Line 94
The multi-part identifier "Inserted.objectGUID" could not be bound.

Msg 4104, Level 16, State 1, Procedure TR_INSERTS_DELTAS, Line 95
The multi-part identifier "INSERTED.distinguishedName" could not be bound.

Msg 4104, Level 16, State 1, Procedure TR_INSERTS_DELTAS, Line 96
The multi-part identifier "INSERTED.adForest" could not be bound.

Msg 4104, Level 16, State 1, Procedure TR_INSERTS_DELTAS, Line 97
The multi-part identifier "INSERTED.adDomain" could not be bound.

Msg 4104, Level 16, State 1, Procedure TR_INSERTS_DELTAS, Line 98
...

What am I doing wrong? :(

Upvotes: 0

Views: 134

Answers (1)

u07ch
u07ch

Reputation: 13702

I think you just need to put a select with the table in rather than using inserted.x to signify the insert.

CREATE TRIGGER [dbo].[TR_INSERTS_DELTAS]
   ON  [dbo].[Users]
   FOR INSERT
AS 
DECLARE @ObjectGUID varbinary(50), @DN varchar(255), @memcount int;
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Get the primary and unique keys from the inserted rows.
    SELECT @DN=i.distinguishedName  FROM inserted i;
    SELECT @ObjectGUID = i.objectGUID FROM inserted i;  

    -- Check if a row already exists in the TDR_Users_Delta table with those values.
    SELECT @memcount=COUNT(*) FROM Users 
    WHERE Users.distinguishedName = @DN 
    AND Users.objectGUID = @ObjectGUID ;
    if(@memcount = 0)
    BEGIN
        INSERT INTO [dbo].[Users_Delta]
        (
            [objectGUID],
            [distinguishedName],
            [adForest],
            [adDomain],
            [accountExpires],
            [adminCount],
            [cn] ,
            [company],
            [description],
            [displayName],
            [division],
            [employeeID],
            [change_type]
        )

        select
            INSERTED.[objectGUID],
            INSERTED.[distinguishedName],
            INSERTED.[adForest],
            INSERTED.[adDomain],
            INSERTED.[accountExpires],
            INSERTED.[adminCount],
            INSERTED.[cn] ,
            INSERTED.[company],
            INSERTED.[description],
            INSERTED.[displayName],
            INSERTED.[division],
            INSERTED.[employeeID],
            'Add'
From inserted
    END
END
GO

Upvotes: 1

Related Questions