SQL Server : trigger error

Question

Could someone solve this please:

CREATE TRIGGER tr_admin_ForInsert
ON admin
FOR INSERT 
AS 
BEGIN 
    DECLARE adminID INT
    SELECT adminID = adminID from inserted

    insert into adminAudit 
    Values ('New Admin with ID = ' + Cast (ID as NVARCHAR(5)) + ' is added at ' + cast(Getdate() as NVARCHAR (20)))
END

I get the error message:

Msg 155, Level 15, State 2, Procedure tr_admin_ForInsert, Line 7
'INT' is not a recognized CURSOR option.

INT is underlined and says '"is not a recognized CURSOR option'
SELECT is underlined and says 'Incorrect syntax near 'SELECT'. Expecting CURSOR, or ID

This is my inserted table for admin:

USE [zachtravelagency]
CREATE TABLE admin
(
        [adminID] INTEGER NOT NULL IDENTITY (1,1) PRIMARY KEY, 
        [firstname] NVARCHAR(30) NOT NULL,
        [surname] NVARCHAR(50) NOT NULL,
        [username] NVARCHAR(30) NOT NULL, 
        [password] NVARCHAR(30) NOT NULL,
);

This is my inserted table for adminAudit:

CREATE table adminAudit 
(
      [adminAuditID] INTEGER NOT NULL IDENTITY (1,1) PRIMARY KEY, 
      [AuditData] NVARCHAR(200) NOT NULL
)

Thanks.

prefix your declaration with @ symbol, i.e. DECLARE adminID INT should be DECLARE @adminID INT — Mox Shah
– Mox Shah, Commented Mar 19, 2015 at 12:57
Unless you are absolutely sure that there will only ever be one admin inserted at a time then this will work; the triggers operate on sets though so doing something like SELECT @adminID = adminID from inserted is doomed to fail as the statement might return more than one record. — jpw
– jpw, Commented Mar 19, 2015 at 13:03
And to add to what @jpw you can't ever be absolutely certain that will be the case. I know a guy who was a consultant at a company that literally went under because they had triggers everywhere that couldn't handle multiple rows. — Sean Lange
– Sean Lange, Commented Mar 19, 2015 at 13:05

Gordon Linoff · Accepted Answer · 2015-03-19 13:00:26Z

6

Don't use a variable at all. Use insert . . . select:

BEGIN 

    insert into adminAudit 
        select 'New Admin with ID = ' + Cast (adminid as NVARCHAR(5)) + ' is added at ' + cast(Getdate() as NVARCHAR(20))
        from inserted;
END

I would also suggest the following:

Use convert() so you have control over the date format.
Better yet, use separate columns in the Audit table for the id, the date, and the operation that you are doing. Putting everything in a string seems inefficient and difficult for investigation purposes.

answered Mar 19, 2015 at 13:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mox Shah · Accepted Answer · 2015-03-19 12:59:07Z

1

prefix your declaration with @ symbol

Update your trigger with this:

 CREATE TRIGGER tr_admin_ForInsert
    ON admin
    FOR INSERT 
    AS 
    BEGIN 

    DECLARE @adminID INT
    SELECT @adminID = adminID from inserted

    insert into adminAudit 
    Values ('New Admin with ID = ' + Cast (ID as NVARCHAR(5)) + ' is added at ' + cast(Getdate() as NVARCHAR (20)))

answered Mar 19, 2015 at 12:59

Mox Shah

3,0153 gold badges29 silver badges44 bronze badges

1 Comment

Sean Lange Over a year ago

While this would "fix" the problem it will generate a mountain of new ones. Scalar variables in a trigger mean the trigger is unable to handle multiple row operations. This should instead be a set based approach.

Brave Soul · Accepted Answer · 2015-03-19 12:59:50Z

0

you have to use @ before variable and pass appropriate value from inserted i.e. i. i.adminID from inserted i

CREATE TRIGGER tr_admin_ForInsert
    ON admin
    FOR INSERT 
    AS 
    BEGIN 

    DECLARE @adminID INT;
    SELECT @adminID = i.adminID from inserted i

    insert into adminAudit 
    Values ('New Admin with ID = ' + Cast (@adminID as NVARCHAR(5)) + ' is added at ' + cast(Getdate() as NVARCHAR (20)))

answered Mar 19, 2015 at 12:59

Brave Soul

3,6504 gold badges33 silver badges51 bronze badges

1 Comment

Sean Lange Over a year ago

While this would "fix" the problem it will generate a mountain of new ones. Scalar variables in a trigger mean the trigger is unable to handle multiple row operations. This should instead be a set based approach.

Collectives™ on Stack Overflow

SQL Server : trigger error

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related