Msg 8101 With trigger

Question

Post reply

Msg 8101 With trigger

toni.harju

SSC Rookie

Points: 46
More actions
July 28, 2010 at 5:16 am

#238141

CREATE TABLE [dbo].[record_table](
[int] IDENTITY(1,1) NOT NULL,
[column1] [char](11) NULL,
[column2] [varchar](14) NULL,
[column3] [int] NULL,
CREATE TABLE [dbo].[deleted_table](
[int] IDENTITY(1,1) NOT NULL,
[column1] [char](11) NULL,
[column2] [varchar](14) NULL,
[column3] [int] NULL,
[date] [datetime] NULL,
[who] [varchar](30) NULL,
CREATE TRIGGER Deleted
ON dbo.record_table
AFTER delete
AS
BEGIN
INSERT dbo.deleted_table
SELECT *, date = getdate()
, who = SUSER_SNAME()
FROM deleted
END
GO
When creating the trigger I get -->
Msg 8101, Level 16, State 1, Procedure MuuttohistoriaDeleted, Line 6
An explicit value for the identity column in table 'dbo.deleted_table can only be specified when a column list is used and IDENTITY_INSERT is ON.
How to fix this?

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

your table dbo.deleted_table, has an identity column in it, don't stick a value in it .

actually name each and every column that SHOULD receive a value from the insert instead.

INSERT dbo.deleted_table(column1,column2,column3,date,who)

SELECT column1,column2,column3, getdate(),SUSER_SNAME()

FROM deleted

now, if you meant to capture the key from the original table, your _deleted table should not be an identitiy...jsut an int.

--only works if table_deleted.key is not identity.

INSERT dbo.deleted_table(key,column1,column2,column3,date,who)

SELECT key,column1,column2,column3, getdate(),SUSER_SNAME()

FROM deleted

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!