Data not being set in Trigger
-
I have a trigger that is fired on a table that is used to update another table with appropriate data from this update, insert or delete. The update statements are appropriately fired off in the trigger, but when the update completes, the data is not in the table that was just updated.
I step through the code in the TSQL debugger and there is no errors and no reason why the data should not be written to the second table. The keys are appropriate, the @@rowcount shows that data was inserted, the trigger on the other table fires and it appears to be updating.
However a select (within the context of the debug session transaction) does not show the data, and normal execution of the update statement from the web application shows no data update to the affected table.
My workaround has been to update the table outside of the trigger, which is not what my customer wants.
What am I doing wrong here or is this one of those server bugs?
-
Hm, it might be helpful to see the code.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sure, an update to the links table fires this trigger. Data is two asset id's of assets being tied together (in the case of the trigger, I am setting an employee name to the location asset (office) that the employee now occupies.
As I have stated earlier, while under debug, the trigger fires and code executes, all variables are populated correctly.
ALTER trigger a_iu_Links on dbo.Links
FOR insert,update,delete AS
BEGIN
declare @Inserted int,
@Deleted int,
@Type char(6),
@EmpName varchar(250),
@loc_id int,
@site_id int,
@category int,
@loccategory int
declare @AssetLink int, -- Link1
@locationLink int -- Link2
declare linkC_Cursor cursor local for select link1_asset_id, link2_asset_id
from inserted i
declare linkD_Cursor cursor local for select link1_asset_id, link2_asset_id
from deleted i
select @inserted = count(*) from inserted
select @deleted = count(*) from deleted
select @type = CASE
When (@inserted > 0) and (@deleted = 0) THEN 'INSERT'
When (@inserted > 0) and (@deleted > 0) THEN 'UPDATE'
When (@inserted = 0) and (@deleted > 0) THEN 'DELETE'
END
if @Type = 'INSERT'
begin
open linkC_Cursor
fetch linkC_Cursor into @AssetLink, @locationLink
while ( @@FETCH_STATUS = 0 )
begin
select @category = category_id, @EmpName = [General/Asset Name] from
assets where asset_id = @assetLink
select @loccategory = category_id from
assets where asset_id = @locationLink
select @loc_id = loc_id, @site_id = site_id
from ullocation where db_id = @locationLink
if (@category = 7) and (@loccategory = 14)
update Assets
SET [General/Notes] = 'Employee: ' + @EmpName,
DateModified = getdate()
where Asset_Id = @locationLink
update Assets
set [General/loc_id] = @loc_id,
[General/locatorcode] = @loc_id,
[General/Site_ID] = @site_id,
DateModified = getdate()
where Asset_id = @assetLink
fetch linkC_Cursor into @AssetLink, @locationLink
end
close linkC_Cursor
end
if @Type = 'UPDATE'
begin
--update Links
--set touched = -1
--from inserted
--where inserted.Link_ID = Links.Link_ID
insert into LinksHistory
(datemodified, [Action], [Link_ID], [link1_asset_ID], [link2_asset_ID], [linkedby], [datelinked])
select getdate() as datemodified, 'PREVIOUS' as [Actions], [Link_ID], [link1_asset_ID], [link2_asset_ID], [linkedby], [datelinked] from deleted
insert into LinksHistory
(datemodified, [Action], [Link_ID], [link1_asset_ID], [link2_asset_ID], [linkedby], [datelinked])
select getdate() as datemodified, 'NEW' as [Actions], [Link_ID], [link1_asset_ID], [link2_asset_ID], [linkedby], [datelinked] from inserted
open linkC_Cursor
fetch linkC_Cursor into @AssetLink, @locationLink
while ( @@FETCH_STATUS = 0 )
begin
select @category = category_id, @EmpName = [General/Asset Name] from
assets where asset_id = @assetLink
select @loccategory = category_id from
assets where asset_id = @locationLink
select @loc_id = loc_id, @site_id = site_id
from ullocation where db_id = @locationLink
if @@rowcount > 0 begin
if (@category = 7) and (@loccategory = 14)
update Assets
SET [General/Notes] = 'Employee: ' + rtrim(@EmpName),
DateModified = getdate()
where Asset_Id = @locationLink
update Assets
set [General/loc_id] = @loc_id,
[General/Site_ID] = @site_id,
DateModified = getdate()
where Asset_id = @assetLink
end
fetch linkC_Cursor into @AssetLink, @locationLink
end
close linkC_Cursor
end
if @Type = 'DELETE'
begin
insert into LinksHistory
(datemodified, [Action], [Link_ID], [link1_asset_ID], [link2_asset_ID], [linkedby], [datelinked])
select getdate() as datemodified, 'DELETED' as [Actions], [Link_ID], [link1_asset_ID], [link2_asset_ID], [linkedby], [datelinked] from deleted
if @deleted = 1
begin
open linkD_Cursor
fetch linkD_Cursor into @AssetLink, @locationLink
while ( @@FETCH_STATUS = 0 )
begin
select @category = category_id, @EmpName = [General/Asset Name] from
assets where asset_id = @assetLink
select @loccategory = category_id from
assets where asset_id = @locationLink
if (@category = 7) and (@loccategory = 14)
update Assets
SET [General/Notes] = 'Vacant',
DateModified = getdate()
where Asset_Id = @locationLink
fetch linkD_Cursor into @AssetLink, @locationLink
end
close linkD_Cursor
end
end
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Geeh, I should have known before...
Well, first I would consider breaking this up into three separate triggers. Not only does this encapsulate the logic a bit more but it also does make the code more readable.
Next, I see no reason here that would justify a cursor anyway.
What exactly does (not) happen?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The reason for the cursor is the fact that there could be multiple updates to the links table at one operation and the atomic operation needs to determine if the asset being linked is an employee to a location (or removed from a location). What does not happen is the update to Assets (although the code executes)
-
What are you trying to catch with
if @@rowcount > 0 begin
in the UPDATE part? @@ROWCOUNT will take the value of the last statement executed, so in your case
select @loc_id = loc_id, @site_id = site_id
from ullocation where db_id = @locationLink
Now, when this doesn't return a row, @@ROWCOUNT gets the value 0 and therefore will not enter the IF clause.
That's the only thing I can think of right now.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply