CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
oginski
oginski

Reputation: 364

T-SQL Incorrect syntax near the keyword 'TRIGGER'

I created instead of trigger like that:

CREATE TRIGGER ReadOnlyEvent ON 
TableName INSTEAD OF INSERT, UPDATE, DELETE AS BEGIN 
RAISERROR('Tables are read only.', 16, 1 ) ROLLBACK TRANSACTION END;

And that code works fine. But when i combine this with 'IF' i got an error:

 IF OBJECT_ID ('ReadOnlyEvent', 'TR') IS NULL 
    CREATE TRIGGER ReadOnlyEvent ON
 TableName INSTEAD OF INSERT, UPDATE, DELETE AS BEGIN 
 RAISERROR('Tables are read only.', 16, 1 ) ROLLBACK TRANSACTION END;

Incorrect syntax near the keyword 'TRIGGER'.

I also tryed IF with BEGIN END:

IF OBJECT_ID ('ReadOnlyEvent', 'TR') IS NULL
BEGIN
    CREATE TRIGGER ReadOnlyEvent ON
    TableName INSTEAD OF INSERT, UPDATE, DELETE AS BEGIN 
    RAISERROR('Tables are read only.', 16, 1 ) ROLLBACK TRANSACTION END;
END;

Why i got this error? :-)

Upvotes: 1

Views: 5249

Answers (3)

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

Standard construction

IF OBJECT_ID ('ReadOnlyEvent', 'TR') IS NOT NULL 
DROP TRIGGER dbo.ReadOnlyEvent
GO
CREATE TRIGGER dbo.ReadOnlyEvent ON
  TableName INSTEAD OF INSERT, UPDATE, DELETE AS BEGIN 
RAISERROR('Tables are read only.', 16, 1 ) ROLLBACK TRANSACTION END;
GO

Upvotes: 5

Pankaj_Dwivedi
Pankaj_Dwivedi

Reputation: 580

Try This one 

IF NOT EXISTS ( SELECT  *  FROM    sys.objects WHERE   type = 'TR'
                    AND name = 'ReadOnlyEvent' ) 

BEGIN
    EXEC ('CREATE TRIGGER ReadOnlyEvent ON
    TableName INSTEAD OF INSERT, UPDATE, DELETE AS BEGIN 
    RAISERROR('Tables are read only.', 16, 1 ) ROLLBACK TRANSACTION END');
END;

Upvotes: 2

i3lai3la
i3lai3la

Reputation: 980

Try this

BEGIN 
    IF OBJECT_ID ('ReadOnlyEvent', 'TR') IS NULL 
        BEGIN
            CREATE TRIGGER ReadOnlyEvent ON
            TableName INSTEAD OF INSERT, UPDATE, DELETE AS BEGIN 
            RAISERROR('Tables are read only.', 16, 1 ) ROLLBACK TRANSACTION END;
        END 
END

Upvotes: 0

Related Questions