Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-26 : 23:13:27
|
Logan writes "I have created a stored procedure that drops triggers and later on creates the triggers, the create trigger statement is inside a varchar variable that is later executed using :
EXECUTE (@TriggerVariable)
The problem is that the trigger will be created on a table that is not the current one, since "Use can not be used inside Stored Procedures, I need a way to acomplish this form within the Stored Procedure.
I tried to add the "Use database name" before the trigger on the variable but it gives an error. Here is the sample code:
/***********************************/
DECLARE @CTrigProc varchar(8000)
SET @CTrigProc = ""
SELECT @CTrigProc ="CREATE TRIGGER [dbo].trg_UpdateEmployee ON [Sample_Temp].[dbo].[Employee]" + Char(13) +
"FOR UPDATE" + Char(13) +
"AS" + Char(13) +
"set nocount on" + Char(13) +
"if update(socialsecurity)" + Char(13) +
" begin" + Char(13) +
" update certification" + Char(13) +
" set certification.socialsecurity = inserted.socialsecurity" + Char(13) +
" from certification, deleted, inserted" + Char(13) +
" where deleted.socialsecurity = certification.socialsecurity" + Char(13) +
" end" + Char(13) +
"" + Char(13) +
"if update(socialsecurity)" + Char(13) +
" begin" + Char(13) +
" update contract" + Char(13) +
" set contract.socialsecurity = contract.socialsecurity" + Char(13) +
" from contract, deleted, inserted" + Char(13) +
" where deleted.socialsecurity = contract.socialsecurity" + Char(13) +
" end" + Char(13) +
"if update(socialsecurity)" + Char(13) +
" begin" + Char(13) +
" update license" + Char(13) +
" set license.socialsecurity = inserted.socialsecurity" + Char(13) +
" from license, deleted, inserted" + Char(13) +
" where deleted.socialsecurity = license.socialsecurity" + Char(13) +
" end"
EXECUTE (@CTrigProc)
Any reference or help will be usefull
Thanks" |
|
|
|
|
|
Creating a trigger in a database that is not the current one inside a stored procedure