Trigger Question How done versus How To???
-
OK, I was hacking through a DB I recently received to review and found the following code in a trigger. I understand the business logic of createing a record in the Loc table when Construct=0 but don't get why the developer used a temp table to hold the PKey rather than sticking it into a local variable and then using that in the insert statement.
Any Idea's? What is the Benefit? Is there a Benefit?
Thanks so much for looking and helping.
Mark
*********************************************
ALTER
TRIGGER [dbo].[Construct_Insert]
ON [dbo].[Construct]
AFTER
INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #SurfTable (PKey INT)
INSERT INTO #SurfTable(PKey)
(SELECT PKey FROM inserted WHERE inserted.Completion=0)
BEGIN
INSERT INTO Loc
(LocType, LinkKey, LinkFrom)
SELECT 'SURF', #SurfTable.PKey, 'CONSTRUCT'
FROM #SurfTable
END
END
-
The only thing I could come up with is that more than 1 row may possibly be inserted when the trigger is fired. If you were to put the key into a variable, you could only insert one record at a time.
Not knowing the system and how data is inserted into dbo.Construct, I can only guess.
-
There is no such thing called, FOR EACH ROW, in SQL server. So, if you want to execute trigger in case more then one rows are inserted into a table, you've to go by this approach.
-
No offense but the temp table part is useless in this exemple (maybe we are not seeing all the code).
-
I think what my esteemed colleague might be trying to say is that there are easier, more natural ways of achieving the same result, without using the temp table. For example, the statement:
INSERT INTO Loc
(LocType, LinkKey, LinkFrom)
SELECT 'SURF', inserted.PKey, 'CONSTRUCT'
FROM inserted
where inserted.completion = 0
is arguably more straightforward.
-
The above was my thoughts on it when I saw the code. Yes this is all of the code in the trigger but from the other responses I can see when multiple records might be inserted. I was even thinking of this on the drive home after posting. Typically this would not be the case from form interaction but when doing a bulk update the possibility exists.
Would the above solution address multiple records in 2005? From my past experience in 2000 I don't think it would. The whole trigger only called once no matter the number of records has burned me more than once in SQL Server.
Thanks so much for all the responses. Great help as usual.
-
Like I said... with a touch of communication skills.
Thanx SDM
. -
Not true. You can use a table variable .... though SDM's query is definitely the better way.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply