SQL trigger fun
-
CREATE TRIGGER tmTaskChangeHist ON Task FOR UPDATE, INSERT NOT FOR REPLICATION
as
begin
set nocount on
if not exists (select * from sysobjects where name = 'TaskChangeHist')
select * into TaskChangeHist from insertedelse
insert TaskChangeHist select * from inserted
set nocount off
endOh my. On top of this we have strict annotation guidelines at my company, which weren't followed there (note lack of comments :P) Can someone please explain the thought process of checking whether TaskChangeHist exists, and then inserting anyway? (not to mention the use of select *, although i guess it could be justified in this context)
-
CREATE TRIGGER tmTaskChangeHist ON Task FOR UPDATE, INSERT NOT FOR REPLICATION
as
begin
set nocount on
if not exists (select * from sysobjects where name = 'TaskChangeHist')
select * into TaskChangeHist from insertedelse
insert TaskChangeHist select * from inserted
set nocount off
endOh my. On top of this we have strict annotation guidelines at my company, which weren't followed there (note lack of comments :P) Can someone please explain the thought process of checking whether TaskChangeHist exists, and then inserting anyway? (not to mention the use of select *, although i guess it could be justified in this context)
Its fairly clear,
ruanr wrote:
if not exists (select * from sysobjects where name = 'TaskChangeHist') select * into TaskChangeHist from inserted
creates TaskChangeHist if it doesn't exist (and inserts the record(s) from inserted), otherwise just insert the records. The use of * is entirely justified (in my opinion) in this case. My only caveat would be that it will break if the Task table definition changes. Not the best code to place in a trigger, but I can't see why you consider it a coding horror?
Bob Ashfield Consultants Ltd
-
Its fairly clear,
ruanr wrote:
if not exists (select * from sysobjects where name = 'TaskChangeHist') select * into TaskChangeHist from inserted
creates TaskChangeHist if it doesn't exist (and inserts the record(s) from inserted), otherwise just insert the records. The use of * is entirely justified (in my opinion) in this case. My only caveat would be that it will break if the Task table definition changes. Not the best code to place in a trigger, but I can't see why you consider it a coding horror?
Bob Ashfield Consultants Ltd
-
Oh - I am a bit of a SQL novice, did not realize that 'select * into' will create a table. If that is the case, why is the 'ELSE' clause necessary? And yes, Task got some extra col's, and so caused problems.
-
Its fairly clear,
ruanr wrote:
if not exists (select * from sysobjects where name = 'TaskChangeHist') select * into TaskChangeHist from inserted
creates TaskChangeHist if it doesn't exist (and inserts the record(s) from inserted), otherwise just insert the records. The use of * is entirely justified (in my opinion) in this case. My only caveat would be that it will break if the Task table definition changes. Not the best code to place in a trigger, but I can't see why you consider it a coding horror?
Bob Ashfield Consultants Ltd
The use of a trigger is coding horror by itself.
-
The use of a trigger is coding horror by itself.