debugsqlModerate
"If not exists" using OBJECT_ID() doesn't work for views and triggers. why?
Viewed 0 times
object_idwhyandtriggersviewsdoesnusingworkexistsfor
Problem
For tables I can implement "if not exists" and "if exists" as folows:
but it is not quite working the same on views and triggers
I can do:
but when I'm trying the oposite:
I'm Getting the following error:
Incorrect syntax near the keyword 'view'
And the same is with triggers. when I do:
I'm getting error:
Incorrect syntax near the keyword 'trigger'
But:
is working.
Did I missed anything?
Can anyone explain this difference between tables to triggers and views?
Note: I'm using sql server 2012
--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 insertedI'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_insis 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
Example:
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 GOExample:
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
GOCode 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
GOContext
StackExchange Database Administrators Q#215699, answer score: 11
Revisions (0)
No revisions yet.