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

Clash Royale CLAN TAG#URR8PPP

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
3
down vote

favorite

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

sql-server trigger view

share|improve this question

edited Aug 23 at 14:51

asked Aug 23 at 14:39

SHR

3371315

• 
  
  
  

  
  

  
  
  
  
  
  

  
  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 at 20:39
  

  
  
  
  

add a comment | 

up vote
3
down vote

favorite

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

sql-server trigger view

share|improve this question

edited Aug 23 at 14:51

asked Aug 23 at 14:39

SHR

3371315

• 
  
  
  

  
  

  
  
  
  
  
  

  
  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 at 20:39
  

  
  
  
  

add a comment | 

up vote
3
down vote

favorite

up vote
3
down vote

favorite

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

sql-server trigger view

share|improve this question

edited Aug 23 at 14:51

asked Aug 23 at 14:39

SHR

3371315

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

sql-server trigger view

share|improve this question

edited Aug 23 at 14:51

asked Aug 23 at 14:39

SHR

3371315

share|improve this question

share|improve this question

edited Aug 23 at 14:51

edited Aug 23 at 14:51

edited Aug 23 at 14:51

asked Aug 23 at 14:39

SHR

3371315

asked Aug 23 at 14:39

SHR

3371315

asked Aug 23 at 14:39

SHR

3371315

3371315

• 
  
  
  

  
  

  
  
  
  
  
  

  
  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 at 20:39
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  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 at 20:39
  

  
  
  
  

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 at 20:39

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 at 20:39

add a comment | 

2 Answers
2

active

oldest

votes

up vote
9
down vote



accepted

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

share|improve this answer

answered Aug 23 at 14:52

Scott Hodgin

15.1k11534

add a comment | 

up vote
6
down vote

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')

share|improve this answer

answered Aug 23 at 15:00

EzLo

2,1911420

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f215699%2fif-not-exists-using-object-id-doesnt-work-for-views-and-triggers-why%23new-answer', 'question_page');

);

Post as a guest

Name Email

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
9
down vote



accepted

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

share|improve this answer

answered Aug 23 at 14:52

Scott Hodgin

15.1k11534

add a comment | 

up vote
9
down vote



accepted

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

share|improve this answer

answered Aug 23 at 14:52

Scott Hodgin

15.1k11534

add a comment | 

up vote
9
down vote



accepted

up vote
9
down vote



accepted

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

share|improve this answer

answered Aug 23 at 14:52

Scott Hodgin

15.1k11534

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

share|improve this answer

answered Aug 23 at 14:52

Scott Hodgin

15.1k11534

share|improve this answer

share|improve this answer

answered Aug 23 at 14:52

Scott Hodgin

15.1k11534

answered Aug 23 at 14:52

Scott Hodgin

15.1k11534

answered Aug 23 at 14:52

Scott Hodgin

15.1k11534

15.1k11534

add a comment | 

add a comment | 

up vote
6
down vote

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')

share|improve this answer

answered Aug 23 at 15:00

EzLo

2,1911420

add a comment | 

up vote
6
down vote

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')

share|improve this answer

answered Aug 23 at 15:00

EzLo

2,1911420

add a comment | 

up vote
6
down vote

up vote
6
down vote

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')

share|improve this answer

answered Aug 23 at 15:00

EzLo

2,1911420

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')

share|improve this answer

answered Aug 23 at 15:00

EzLo

2,1911420

share|improve this answer

share|improve this answer

answered Aug 23 at 15:00

EzLo

2,1911420

answered Aug 23 at 15:00

EzLo

2,1911420

answered Aug 23 at 15:00

EzLo

2,1911420

2,1911420

add a comment | 

add a comment | 

 

draft saved

draft discarded

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f215699%2fif-not-exists-using-object-id-doesnt-work-for-views-and-triggers-why%23new-answer', 'question_page');

);

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Name Email

Name

Email

Name Email

Name

Email