Solved

MS SQL syntax question

Posted on 2007-04-10
2
197 Views
Last Modified: 2010-03-19
What is the correct syntax for this SP?   I want to create a SP to recreate some triggers in a DB.   Here is what I have tried.

CREATE PROCEDURE dbo.CreateTriggersInPL2DB
      
AS

      
create TRIGGER PL2Enhancement_addEbayAuction
ON POWERLINK.dbo.INVENTORY
FOR insert
AS
insert into PLEnhancements.dbo.ebayListingQueue

(
InventoryID,
CreatedOn,
ActionToPerform,
Status
)

select
inventoryID,
getdate(),
'addItem',
null

from inserted

Create TRIGGER PL2Enhancement_addProductToSF
ON dbo.INVENTORY
FOR insert
AS
declare @inventoryID int
select @InventoryID=inventoryid from inserted

exec PLEnhancements.dbo.InsertProductIntoSFbyInventoryID @InventoryID
      
RETURN
0
Comment
Question by:pamsauto
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18882147
CREATE scripts have to be isolated, with the exception of temp tables.
so the only solution is to make it dynamic sql:


CREATE PROCEDURE dbo.CreateTriggersInPL2DB
     
AS

exec ('      
create TRIGGER PL2Enhancement_addEbayAuction
ON POWERLINK.dbo.INVENTORY
FOR insert
AS
insert into PLEnhancements.dbo.ebayListingQueue

(
InventoryID,
CreatedOn,
ActionToPerform,
Status
)

select
inventoryID,
getdate(),
''addItem'',
null

from inserted
')

exec ('
Create TRIGGER PL2Enhancement_addProductToSF
ON dbo.INVENTORY
FOR insert
AS
declare @inventoryID int
select @InventoryID=inventoryid from inserted

exec PLEnhancements.dbo.InsertProductIntoSFbyInventoryID @InventoryID
')
     
RETURN

0
 
LVL 27

Expert Comment

by:ptjcb
ID: 18882156
Why are you trying to do this within a stored procedure? Why not just script the triggers? Are you planning to run this stored procedure often?

0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now