Solved

MS SQL syntax question

Posted on 2007-04-10
2
205 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

697 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