SQL Server – Why ‘If Not Exists’ Using OBJECT_ID() Doesn’t Work for Views and Triggers

sql servertriggerview

For tables I can implement "if not exists" and "if exists" as folows:

--if table exists - drop
If OBJECT_ID('A','U') is not null
Drop Table [A]
--if table not exists - Create
If OBJECT_ID('A','U') is null
Create Table A([key] varchar(20), [value] varchar(max))

but it is not quite working the same on views and triggers

I can do:

-- if exists - drop
If OBJECT_ID('VA','V') is not null
Drop view [VA]

but when I'm trying the oposite:

-- if not exists - create
If OBJECT_ID('VA','V') is null
Create view [VA] as Select * from [A]

I'm Getting the following error:

Incorrect syntax near the keyword 'view'

And the same is with triggers. when I do:

-- if not exists - create
If OBJECT_ID('Trigger_A_ins','TR') is null
Create trigger [Trigger_A_ins] On [A] instead of insert As 
   insert into A select * from inserted

I'm getting error:

Incorrect syntax near the keyword 'trigger'

But:

-- if exists - drop
If OBJECT_ID('Trigger_A_ins','TR') is not null
Drop Trigger Trigger_A_ins

is working.

Did I missed anything?

Can anyone explain this difference between tables to triggers and views?

Note: I'm using sql server 2012

Best Answer

Referencing the documentation from CREATE VIEW under REMARKS:

The CREATE VIEW must be the first statement in a query batch.

Referencing the documentation from CREATE TRIGGER

CREATE TRIGGER must be the first statement in the batch and can apply to only one table.

For VIEWS and TRIGGERS, I think you'll have to check for the existence of the object and drop in one batch and create them in another batch separated by a GO

Example:

IF EXISTS (SELECT * FROM sys.objects WHERE [name] = '[dbo].[trg]' AND [type] = 'TR')
      DROP TRIGGER [dbo].[trg] ON [dbo].[tbl]
GO
CREATE TRIGGER [dbo].[trg] ON [dbo].[tbl] 
AFTER DELETE
AS
BEGIN
   //
END
GO

AFTER INSERT:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAI$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAI` AFTER INSERT ON `TestTable` FOR EACH ROW
    BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM TestTable2 WHERE (ItemId=NEW.ItemId AND Year=YEAR(NEW.Date) AND Month=MONTH(NEW.Date)));
    IF @COUNT=0 THEN
        INSERT INTO TestTable2 (ItemId,Year,Month,Open,Close,Increase ) VALUES 
        (NEW.ItemId , YEAR(NEW.Date), MONTH(NEW.Date),NEW.Value,NEW.Close,0.0); 
    ELSE
        UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
        Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
        WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));
    END IF;
    END;
$$
DELIMITER ;

AFTER UPDATE:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAU$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAU` AFTER UPDATE ON `TestTable` FOR EACH ROW
BEGIN

    UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
    Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
    WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));

END;
$$
DELIMITER ;

Note: Be careful about the SELECT Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)), I've added a LIMIT 1 to avoid duplicate rows.

Change the database TEST.

You can also use ON DUPLICATE KEY on the two triggers execution.

You can get more information in the MySQL's documentation manual.

Best Answer

Related Solutions

PostgreSQL – Tracking Current User Through Views and Triggers

MySQL Trigger – How to Create Trigger for Update or Insert in Another Table

AFTER INSERT:

AFTER UPDATE:

Related Question