0

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.

  • 2
    I don't think you can create a database level trigger for DML instructions, only for DDL (create, alter, drop...). You will have to capture the insert or update for you table. – Renato Afonso Jul 27 '17 at 10:07
4

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.

  • You Sir, are a genius! I applied your code above (however the part from CREATE TRIGGER until GO) and it works fine! – Bruder Lustig Jul 27 '17 at 10:31
  • 1
    Nope, just experience. I've a long way to go before I even get counted as smart. Glad it works for you though, no go test it! :) – Shaneis Jul 27 '17 at 10:35
  • 1
    With "it works fine!" I of course already meant thath the trigger reacts correctly on wrongful insert attempts. ;-) – Bruder Lustig Jul 27 '17 at 10:38

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.