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
DROP TRIGGER IF EXISTS
(similar for tables, views, etc.).