How do I create a trigger in a stored procedure?
- From: "Mark B" <none123@xxxxxxxx>
- Date: Fri, 28 Aug 2009 16:10:21 +1200
I want a stored procedure to:
1) Delete existing trigger on TableA
2) Run an Update SQL for certain fields in TableA (cleaning table)
3) Recreate the trigger
It's giving me the following error though:
Msg 156, Level 15, State 1, Procedure uspGeneralLanguageMaintenanceAutoValuesClean, Line 19
Incorrect syntax near the keyword 'Trigger'.
ALTER PROCEDURE [dbo].[uspGeneralLanguageMaintenanceAutoValuesClean]
AS
/****** 1)
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id =
OBJECT_ID(N'[dbo].[LanguageValuesTrigger]'))
DROP TRIGGER [dbo].[LanguageValuesTrigger]
/****** 2)
UPDATE tblLanguageValues
SET LanguageText = REPLACE(LanguageText,'"', '"')
WHERE (LanguageCode <> 'EN-US')
UPDATE tblLanguageValues
SET LanguageText = REPLACE(LanguageText,''', char(39))
WHERE (LanguageCode <> 'EN-US')
/****** 3)
create Trigger [dbo].[LanguageValuesTrigger] On [dbo].[tblLanguageValues]
After Update As
Begin
Update LV
Set ToDo = 1, LastUpdatedBy = 'Needs Refresh'
From dbo.tblLanguageValues LV
Inner Join inserted i On LV.LookupID = i.LookupID
Inner Join deleted d On i.LookupID = d.LookupID And i.LanguageText <>
d.LanguageText
Where i.LanguageCode = 'en-US'
And LV.LanguageCode <> 'en-US';
End;
.
- Follow-Ups:
- Re: How do I create a trigger in a stored procedure?
- From: Erland Sommarskog
- Re: How do I create a trigger in a stored procedure?
- From: Tom Cooper
- RE: How do I create a trigger in a stored procedure?
- From: Linchi Shea
- Re: How do I create a trigger in a stored procedure?
- Prev by Date: Re: [NOW ANSWER Aaron's QUESTION CELKO]
- Next by Date: Re: Good book (advanced) on query optimization for SQL Server 2005
- Previous by thread: Good book (advanced) on query optimization for SQL Server 2005
- Next by thread: RE: How do I create a trigger in a stored procedure?
- Index(es):
Relevant Pages
|
