sql azure: trigger error when inserting into temp table from inserted table

Question

I have a trigger on a sql azure table I am trying to set up. Bear with me as this is one of my first triggers. This trigger is supposed to track all changes to the table in an audit table.

As I understand, you can only use temporary tables in SQL azure if you specifically create them. Documented here
Also, as I understand, the inserted and deleted tables that are available within a trigger match the schema of the table the trigger is set up on. documented here

So I created a trigger on my table and in that trigger I create two temp tables, one for the inserted and dleted tables, with matching schemas.

When I try to insert into those temp tables from the inserted or deleted tables I get the following error:

The data in row 3 was not commited
Error Source: Microsfoft.SqlServer.Management.DataTools
Error Message: The row values(s) updated or deleted either do not make the row unique or they alter multimple rows (4 rows).

This error occurs on these lines:

INSERT INTO #tempIns SELECT * FROM inserted
INSERT INTO #tempDel SELECT * FROM deleted

Here is my full tirgger:

ALTER TRIGGER [dbo].[tr_KrisisShifts_ShiftTrade] ON [dbo].[KrisisShifts_ShiftTrade] FOR INSERT, UPDATE, DELETE
AS

DECLARE @bit INT ,
       @field INT ,
       @maxfield INT ,
       @char INT ,
       @fieldname VARCHAR(128) ,
       @TableName VARCHAR(128) ,
       @PKCols VARCHAR(1000) ,
       @sql VARCHAR(2000), 
       @UpdateDate VARCHAR(21) ,
       @UserName VARCHAR(128) ,
       @Type CHAR(1) ,
       @PKSelect VARCHAR(1000),
       @RecordId VARCHAR(128),     
       @UserId VARCHAR(128)

--You will need to change @TableName to match the table to be audited
SELECT @TableName = 'KrisisShifts_ShiftTrade'

-- date and user
SELECT         @UserName = SYSTEM_USER ,
       @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) 
               + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

---- Action
IF EXISTS (SELECT * FROM inserted)
       IF EXISTS (SELECT * FROM deleted)
               SELECT @Type = 'U'
       ELSE
               SELECT @Type = 'I'
ELSE
       SELECT @Type = 'D'

---- get list of columns
CREATE TABLE #tempIns (
    [ShiftID] [int] PRIMARY KEY NOT NULL,
    [ShiftGUID] [uniqueidentifier] NOT NULL,
    [PortalID] [int] NULL,
    [DepartmentID] [int] NULL,
    [PlatoonID] [int] NULL,
    [BranchID] [int] NULL,
    [TradeDate] [date] NULL,
    [StatusID] [int] NULL,
    [LastActionDate] [datetime] NULL,
    [AllowedRankID] [nvarchar](20) NULL,
    [OwnerUserID] [int] NULL,
    [OwnerEmail] [nvarchar](50) NULL,
    [OwnerLocationID] [int] NULL,
    [OwnerRankID] [int] NULL,
    [OwnerPlatoonID] [int] NULL,
    [OwnerEmployeeID] [nvarchar](50) NULL,
    [WorkerUserID] [int] NULL,
    [WorkerEmail] [nvarchar](50) NULL,
    [WorkerLocationID] [int] NULL,
    [WorkerRankID] [int] NULL,
    [WorkerPlatoonID] [int] NULL,
    [WorkerEmployeeID] [nvarchar](50) NULL,
    [SupervisorEmail] [nvarchar](50) NULL,
    [SupervisorRankID] [int] NULL,
    [SupervisorUserID] [int] NULL,
    [SupervisorEmployeeID] [nvarchar](50) NULL,
    [SupervisorApproval] [bit] NULL,
    [Detail] [nvarchar](max) NULL,
    [IsPartialShift] [bit] NULL,
    [LastModifiedByUserID] [int] NULL,
    [confirmationGUID] [uniqueidentifier] NULL,
    [Archived] [bit] NULL,
    [CreatedDate] [datetime] NULL,
    [UpdatedDate] [datetime] NULL);

CREATE TABLE #tempDel (
    [ShiftID] [int] PRIMARY KEY NOT NULL,
    [ShiftGUID] [uniqueidentifier] NOT NULL,
    [PortalID] [int] NULL,
    [DepartmentID] [int] NULL,
    [PlatoonID] [int] NULL,
    [BranchID] [int] NULL,
    [TradeDate] [date] NULL,
    [StatusID] [int] NULL,
    [LastActionDate] [datetime] NULL,
    [AllowedRankID] [nvarchar](20) NULL,
    [OwnerUserID] [int] NULL,
    [OwnerEmail] [nvarchar](50) NULL,
    [OwnerLocationID] [int] NULL,
    [OwnerRankID] [int] NULL,
    [OwnerPlatoonID] [int] NULL,
    [OwnerEmployeeID] [nvarchar](50) NULL,
    [WorkerUserID] [int] NULL,
    [WorkerEmail] [nvarchar](50) NULL,
    [WorkerLocationID] [int] NULL,
    [WorkerRankID] [int] NULL,
    [WorkerPlatoonID] [int] NULL,
    [WorkerEmployeeID] [nvarchar](50) NULL,
    [SupervisorEmail] [nvarchar](50) NULL,
    [SupervisorRankID] [int] NULL,
    [SupervisorUserID] [int] NULL,
    [SupervisorEmployeeID] [nvarchar](50) NULL,
    [SupervisorApproval] [bit] NULL,
    [Detail] [nvarchar](max) NULL,
    [IsPartialShift] [bit] NULL,
    [LastModifiedByUserID] [int] NULL,
    [confirmationGUID] [uniqueidentifier] NULL,
    [Archived] [bit] NULL,
    [CreatedDate] [datetime] NULL,
    [UpdatedDate] [datetime] NULL);

INSERT INTO #tempIns SELECT * FROM inserted
INSERT INTO #tempDel SELECT * FROM deleted

-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') 
               + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
               INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = 'PRIMARY KEY'
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+'+','') 
       + '''<' + COLUMN_NAME 
       + '=''+convert(varchar(100),
coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>''' 
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
               INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = 'PRIMARY KEY'
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary Key value for record
SELECT @RecordId = '+convert(varchar(100),
coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))' 
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
               INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = 'PRIMARY KEY'
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get User Id value for record
SELECT @UserId = '+convert(varchar(100),
coalesce(i.LastModifiedByUserID,d.LastModifiedByUserID,0))' 
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
               INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = 'PRIMARY KEY'
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

IF @PKCols IS NULL
BEGIN
       RAISERROR('no PK on table %s', 16, -1, @TableName)
       RETURN
END

SELECT @field = 0, 
       @maxfield = MAX(ORDINAL_POSITION) 
       FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName

WHILE @field < @maxfield
BEGIN
    SELECT @field = MIN(ORDINAL_POSITION) 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = @TableName 
            AND ORDINAL_POSITION > @field

    SELECT @bit = (@field - 1 )% 8 + 1

    SELECT @bit = POWER(2,@bit - 1)

    SELECT @char = ((@field - 1) / 8) + 1

    IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0 OR @Type IN ('I','D')
    BEGIN
        SELECT @fieldname = COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_NAME = @TableName 
                AND ORDINAL_POSITION = @field

        SELECT @sql = '
            insert krisisShifts_Audit (  Type, 
                            TableName, 
                            PK, 
                            FieldName, 
                            OldValue, 
                            NewValue, 
                            UpdateDate, 
                            UserName,
                            RecordId,
                            UserId)
            select ''' + @Type 
                    + ''',''' + @TableName 
                    + ''',' + @PKSelect
                    + ',''' + @fieldname + ''''
                    + ',convert(varchar(1000),d.' + @fieldname + ')'
                    + ',convert(varchar(1000),i.' + @fieldname + ')'
                    + ',''' + @UpdateDate + ''''
                    + ',''' + @UserName 
                    + ''',' +  @RecordId
                    + ','   +  @UserId
                    + ' from #tempIns i full outer join #tempDel d'
                    + @PKCols
                    + ' where i.' + @fieldname + ' <> d.' + @fieldname 
                    + ' or (i.' + @fieldname + ' is null and  d.'
                                            + @fieldname
                                            + ' is not null)' 
                    + ' or (i.' + @fieldname + ' is not null and  d.' 
                                            + @fieldname
                                            + ' is null)' 
        EXEC (@sql)
    END

END

QUESTION

Can someone help me figure out why I can not insert into my temp tables inside this trigger

thanks in advance

Alberto Morillo · Accepted Answer · 2017-06-21 01:24:04Z

2

When you are inserting records to the KrisisShifts_ShiftTrade table you can read from the inserted (logical table) but you should not use the deleted (logical table). My suggestion is create a separate trigger just for inserting records since you are beginning to work with triggers.

When you are deleting records from the the KrisisShifts_ShiftTrade table you then should use only the deleted (logical table). Try creating a trigger just for deleting.records.

Finally when you are updating records on the KrisisShifts_ShiftTrade table you should read from both logical tables, from the inserted and from the deleted tables. Again try creating a trigger just for updating records.

It seems you are using the following article to learn about triggers.

https://weblogs.asp.net/jongalloway/adding-simple-trigger-based-auditing-to-your-sql-server-database

Hope this helps.

Regards,

Alberto Morillo

SQLCoffee.com

answered Jun 21, 2017 at 1:24

Alberto Morillo

1,8179 silver badges9 bronze badges

yeah, that article is all over the web and has been quoted in stack overflow many times. It is what I started with. I have it working great on my local instance of SQL server but ran into this issue with Azure SQL. In truth, after some reading over the last hour I think I will be moving towards temporal tables, they seem fantastic.
– J King
Commented Jun 21, 2017 at 1:26
Please allow me to share the following article with you now that you are going to use temporal tables. sqlcoffee.com/Azure_0013.htm
– Alberto Morillo
Commented Jun 21, 2017 at 2:59

Add a comment |

Stack Exchange Network

sql azure: trigger error when inserting into temp table from inserted table

QUESTION

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
t-sql
trigger
azure-sql-database
or ask your own question.

Hot Network Questions

sql azure: trigger error when inserting into temp table from inserted table

QUESTION

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged t-sqltriggerazure-sql-database or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
t-sql
trigger
azure-sql-database
or ask your own question.