What is missing in my trigger?

Question

I have a table in which view names can be inserted and the following trigger should ensure that only vew_names can be inserted that really exist in the System (that's all the code the trigger consists of):

CREATE TRIGGER check_if_view_exists ON DATABASE FOR [INSERT], [UPDATE] 
AS 
BEGIN
   IF EXISTS(SELECT view_name from MySpecial_views EXCEPT SELECT name from sys.views)
       RAISERROR ('You can only insert views in that table that really exist!',10, 1) 
      ROLLBACK
   END -- end if 
END -- end trigger

However upon creation of the trigger I get the following errors:

Msg 102, Level 15, State 1, Procedure check_if_view_exists, Line 1 [Batch Start Line 0] Incorrect syntax near 'INSERT'.

Msg 102, Level 15, State 1, Procedure check_if_view_exists, Line 8 [Batch Start Line 0] Incorrect syntax near 'END'.

What is missing? I have carefully read the grammar of T-SQL Triggers but can't figure out why my trigger is wrong.

Answer 1

Note: As far as I am aware, you cannot create a database trigger as you are currently attempting to do...

That being said, your 2 error messages, "Incorrect syntax near 'INSERT'" and "Incorrect syntax near 'END'", are due to you using square brackets [] and missing a starting BEGIN respectively.

Your Code (with comments):

--Remove these Square Brackets...
CREATE TRIGGER check_if_view_exists ON DATABASE FOR [INSERT], [UPDATE] 
AS 
BEGIN
   IF EXISTS(SELECT view_name from MySpecial_views EXCEPT SELECT name from sys.views)
       -- You don't END an IF, you END a BEGIN
       RAISERROR ('You can only insert views in that table that really exist!',10, 1) 
      ROLLBACK
   END -- end if 
END -- end trigger

Working Example:

CREATE TABLE dbo.MySpecialViews (
    ViewName varchar(10)
);
GO

-- Table or View, not on the Database
-- ...also no [] around INSERT and UPDATE
CREATE TRIGGER CheckIfViewExists ON dbo.MySpecialViews FOR INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT ViewName FROM MySpecialViews EXCEPT SELECT name FROM sys.views)
    -- Added a BEGIN for the code after the IF
    BEGIN
        RAISERROR('you can only insert views in that table that really exist!', 10, 1)
        ROLLBACK
    END
END;
GO

DROP TABLE dbo.MySpecialViews;

Hope this helps.