HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlModerate

"If not exists" using OBJECT_ID() doesn't work for views and triggers. why?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
object_idwhyandtriggersviewsdoesnusingworkexistsfor

Problem

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

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#215699, answer score: 11

Revisions (0)

No revisions yet.