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

Answer 1

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

• J King: 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.
• Alberto Morillo: Please allow me to share the following article with you now that you are going to use temporal tables. sqlcoffee.com/Azure_0013.htm