Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Trigger Error 8101

uptownmissyuptownmissy Member Posts: 4
Hi,

I have a trigger to update a table name StatementLog. In the StatementLog table the field being inserted is called AccountID, however in the same table I have a field which is called StmtLogID which is an auto number. I have the following trigger:
CREATE TRIGGER AccountID ON Accounts
FOR INSERT, UPDATE, DELETE
AS
DECLARE @WhatHappened CHAR(1) DECLARE @BeenDeleted CHAR(1) DECLARE @BeenInserted CHAR(1)
IF (SELECT 1 WHERE EXISTS(SELECT * FROM deleted)) = 1 SELECT @BeenDeleted = 'Y'
IF (SELECT 1 WHERE EXISTS(SELECT * FROM inserted)) = 1 SELECT @BeenInserted = 'Y'
IF (@BeenInserted = 'Y') AND (@BeenDeleted IS NULL) SELECT @WhatHappened = 'I'
IF (@BeenInserted IS NULL) AND (@BeenDeleted = 'Y') SELECT @WhatHappened = 'D'
IF (@BeenInserted = 'Y') AND (@BeenDeleted = 'Y') SELECT @WhatHappened = 'U'
IF (@WhatHappened = 'I')
BEGIN
INSERT StatementLog
SELECT NULL, i.accountid, NULL, NULL, NULL, GETDATE()
FROM inserted i
END

It is not allowing the Insert since the table I want to insert a record in has a field that is an auto number. How do I change my code?
The error I get is Error 8101: An explicit value for the identity column in table 'StatementLog' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Thanks!

Comments

  • ShanooShanoo Member Posts: 32
    Hi!

    The error it is giving is obvious. Autonumber columns automatically generate the next value during the insertion of new row. You can insert any value explicitly in an Auto Number column only when IDENTITY_INSERT option is ON and you are using a column list. In your example, modify the trigger as follows:

    CREATE TRIGGER AccountID ON Accounts
    FOR INSERT, UPDATE, DELETE
    AS
    DECLARE @WhatHappened CHAR(1)
    DECLARE @BeenDeleted CHAR(1)
    DECLARE @BeenInserted CHAR(1)
    IF (SELECT 1 WHERE EXISTS(SELECT * FROM deleted)) = 1 SELECT @BeenDeleted = 'Y'
    IF (SELECT 1 WHERE EXISTS(SELECT * FROM inserted)) = 1 SELECT @BeenInserted = 'Y'
    IF (@BeenInserted = 'Y') AND (@BeenDeleted IS NULL) SELECT @WhatHappened = 'I'
    IF (@BeenInserted IS NULL) AND (@BeenDeleted = 'Y') SELECT @WhatHappened = 'D'
    IF (@BeenInserted = 'Y') AND (@BeenDeleted = 'Y') SELECT @WhatHappened = 'U'
    IF (@WhatHappened = 'I')
    BEGIN
    SET IDENTITY_INSERT StatementLog ON

    INSERT StatementLog ()
    SELECT NULL, i.accountid, NULL, NULL, NULL, GETDATE()
    FROM inserted i

    SET IDENTITY_INSERT StatementLog OFF
    END

    Alternatively, you can simply pass the values for non Identity columns in the insert statement by omitting the value for Identity column if you do not want to insert any explicit value in the Identity column.This will also work.

    Happy programming!!!
Sign In or Register to comment.