SQL Server Trigger – What Is Missing in My Trigger?

sql-server-2016ssmst-sqltrigger

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.

Best Answer

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.

Related Solutions

Sql-server – Updating indexes and stats script

My honest opinion is that - Don't reinvent the wheel as there is cost to reinvent it.

Highly suggest you to use Ola's index and stats maintenance solution.

It is very flexible and is highly awarded and tested. Also it does not use any of ms_foreach stuff that you have in your script.

SQL Server – Query with Date Ranges

Not sure where you copied this code from, but it looks like it came from a web site that translated minus signs (-) to long dashes (–). You can see this in Management Studio if you turn IntelliSense on:

Here is the easy way to compare the same range as the year before:

DECLARE @startdate date = '20170101',
        @enddate   date = '20170212';

SELECT @startdate, DATEADD(YEAR, -1, @startdate),
       @enddate,   DATEADD(YEAR, -1, @enddate);

This compares the same period as last year, but I'm not sure that's what you're after since you are also using the zero date and datediff - not clear why. It does not account for leap year; you'll need to define what should happen if the range involves 2/29 either this year or last year.

When you use those dates in a query, don't do this subtracting 3 milliseconds nonsense - it only leads to problems. If you're trying to get all the data for February 12th even if it has time, then use < DATEADD(DAY, 1, @enddate). Also, don't use YY, just type out YEAR. It is also far easier to get the full range of last year:

-- for SQL Server 2012+
WHERE col >= DATEFROMPARTS(YEAR(@startdate)-1, 1, 1)
  AND col <  DATEFROMPARTS(YEAR(@startdate), 1, 1);

-- for older, unsupported versions:
WHERE col >= DATEADD(YEAR, YEAR(@startdate)-1901, '19000101'),
  AND col <  DATEADD(YEAR, YEAR(@startdate)-1900, '19000101');

To sum up:

DECLARE @startdate date = '20170101',
        @enddate   date = '20170212';

-- sales from that period this year:

SELECT SUM(some_column) FROM dbo.SomeTable
  WHERE some_datetime_column >= @startdate
    AND some_datetime_column <  DATEADD(DAY, 1, @enddate);

-- sales from same period previous year (again, not accounting for leap special case):

SELECT SUM(some_column) FROM dbo.SomeTable
  WHERE some_datetime_column >= DATEADD(YEAR, -1, @startdate)
    AND some_datetime_column <  DATEADD(YEAR, -1, DATEADD(DAY, 1, @enddate));

-- sales from all of previous year:

SELECT SUM(some_column) FROM dbo.SomeTable
  WHERE some_datetime_column >= DATEADD(YEAR, YEAR(@startdate)-1901, '19000101'),
    AND some_datetime_column <  DATEADD(YEAR, YEAR(@startdate)-1900, '19000101');

Best Answer

Related Solutions

Sql-server – Updating indexes and stats script

SQL Server – Query with Date Ranges

Related Question