5

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

  • While it doesn't help you on 2012, for those on 2016+: you can now use DROP TRIGGER IF EXISTS (similar for tables, views, etc.). – Jon of All Trades Aug 23 '18 at 20:39
10

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
7

If you still need this on the same batch, you can use dynamic SQL.

If OBJECT_ID('vTest','V') is not null
    DROP VIEW vTest

EXEC('CREATE VIEW vTest AS SELECT TOP 1 * FROM SomeTable')

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.