"If not exists" using OBJECT_ID() doesn't work for views and triggers. why?
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
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')