can't crate trigger in stored procedure
-
hi all, i want to create a trigger on table through a stored procedure but i got error in excuting the script it say syntax problem near word TRIGGER CREATE PROCEDURE CreateMyTrigger -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; CREATE TRIGGER [dbo].[InsertQuestionTrigger] ON [dbo].[Question] FOR INSERT AS UPDATE child -- set the depth of this "child" to be the -- depth of the parent, plus one. SET depth = ISNULL(parent.depth + 1,0), -- the lineage is simply the lineage of the parent, -- plus the child's ID (and appropriate '/' characters lineage = ISNULL(parent.lineage,'/') + LTrim(Str(child.QuestionId)) + '/' -- we can't update the "inserted" table directly, -- so we find the corresponding child in the -- "real" table FROM Question child INNER JOIN inserted i ON i.QuestionId=child.QuestionId -- now, we attempt to find the parent of this -- "child" - but it might not exist, so these -- values may well be NULL LEFT OUTER JOIN Question parent ON child.IdParent=parent.QuestionId END GO Any Idea and how can create trigger using function or stored procedure. Thank you in advance!
-
hi all, i want to create a trigger on table through a stored procedure but i got error in excuting the script it say syntax problem near word TRIGGER CREATE PROCEDURE CreateMyTrigger -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; CREATE TRIGGER [dbo].[InsertQuestionTrigger] ON [dbo].[Question] FOR INSERT AS UPDATE child -- set the depth of this "child" to be the -- depth of the parent, plus one. SET depth = ISNULL(parent.depth + 1,0), -- the lineage is simply the lineage of the parent, -- plus the child's ID (and appropriate '/' characters lineage = ISNULL(parent.lineage,'/') + LTrim(Str(child.QuestionId)) + '/' -- we can't update the "inserted" table directly, -- so we find the corresponding child in the -- "real" table FROM Question child INNER JOIN inserted i ON i.QuestionId=child.QuestionId -- now, we attempt to find the parent of this -- "child" - but it might not exist, so these -- values may well be NULL LEFT OUTER JOIN Question parent ON child.IdParent=parent.QuestionId END GO Any Idea and how can create trigger using function or stored procedure. Thank you in advance!
-
Instead of writing the trigger creation directly to the stored procedure, put it in a varchar variable. Then you can use EXECUTE[^] to execute the string.
The need to optimize rises from a bad design.My articles[^]
for example; declare @query varchar(500) set @query = "your trigger create here, I would suggest using not exists clause to prevent you from trying to create it when it is already created :)" EXECUTE @query Good luck