CODEWITHSUNDEEP
sqlsql-servertriggersuniqueidentifierdatabase-trigger
RAM
RAM

Reputation: 485

How can I prevent a record inserted by an SQL trigger attempting to set the identity column

I'm attempting to create a 'history' table that gets updated every time a row on the source table is updated.

Here's the (SQL Server) code I'm using to create the history table:

DROP TABLE eventGroup_History 

SELECT 
    CAST(NULL AS UNIQUEIDENTIFIER) AS NewId, 
    CAST(NULL AS varchar(255)) AS DoneBy, 
    CAST(NULL AS varchar(255)) AS Operation, 
    CAST(NULL AS datetime) AS DoneAt, 
    * 
INTO 
    eventGroup_History 
FROM 
    eventGroup 
WHERE 
    1 = 0
GO

ALTER TABLE eventGroup_History 
ALTER COLUMN NewId UNIQUEIDENTIFIER NOT NULL
go

ALTER TABLE eventGroup_History 
ADD PRIMARY KEY (NewId)
GO

ALTER TABLE eventGroup_History 
ADD CONSTRAINT DF_eventGroup_History_NewId DEFAULT NewSequentialId() FOR NewId
GO

The trigger is created like this:

drop trigger eventGroup_LogUpdate
go

create trigger eventGroup_LogUpdate 
on dbo.eventGroup
for update
as
  declare @Now as DateTime = GetDate()
  set nocount on

  insert into eventGroup_History
      select @Now, SUser_SName(), 'update-deleted', *
      from deleted

  insert into eventGroup_History
      select SUser_SName(), 'update-inserted', @Now, *
      from inserted
go

exec sp_settriggerorder @triggername = 'eventGroup_LogUpdate', @order = 'last', @stmttype = 'update'

But when I update a row in SQL Server Management Studio, I get a message:

The data in row 2 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: Conversion failed when converting from a character string to uniqueidentifier.

enter image description here

I think that the trigger is attempting to insert the SUserSName() as the first column of the row but that is the PK NewId:

enter image description here

There are no other uniqueidentifier columns in the table.

If I add row from the SQL Management Studio's edit grid, the row gets added without me having to specify the NewId value.

So, why is the SQL Server trigger attempting to populate NewId with first item in the INSERT INTO clause rather than skipping it to let the normal IDENTITY operation provide a value?

(And how do I stop this happening so that the trigger works?)

Upvotes: 0

Views: 381

Answers (2)

Eric Brandt
Eric Brandt

Reputation: 8101

If you're going to use a default value on your NewId column, you need to explicitly list the column names in the INSERT statements. By default, SQL Server will insert the columns in the order they're listed in the SELECT, unless you give it enough information to do otherwise. Listing out the columns explicitly is a best practice, one way or the other, in order to avoid just this sort of unanticipated result.

So your statements will end up looking like this:

INSERT INTO eventGroup_History
  (
    DoneBy,
    Operation,
    DoneAt,
    <All the other columns that are masked by the *>
  )
SELECT....

Upvotes: 3

motosubatsu
motosubatsu

Reputation: 445

Because the automatic skipping only applies to IDENTITY columns - a GUID column set with the NewSequentialId() constraint behaves similarly to IDENTITY in many ways but not this one.

You can achieve what you are looking for by specifying the columns for the INSERT explicitly.

Upvotes: 3

Related Questions