Help With Trigger

[MISdad]

I need some help. I wrote a trigger that will change a value in a table from Y to N and back again based on a record being inserted or updated in another table. My problem is that it doesn't seem to work consistently. It will change the value sometimes but not all the time. My frustration is in the fact that if it works on some records, it should work on all of them. I guess I need another set of eyes to look at this thing. Here is the detail, including the trigger code. Any help is greatly appreciated.


As you can see, there are 3 tables referenced. This is because there is not a direct link from the es_lockrecords table to the activwork table. The es_lockcolumns table is used to create the proper linking so that only the currently selected record is updated. Again, the problem is that this trigger only seems to run on some records and not all of them.

create trigger cd.t_eslockrecords on echo.es_lockrecords
for insert, update
as

declare @status char (2)
declare @lockrecordid char (18)
declare @lockcolumnvalue char (18)
declare @lockcolumnrid char (18)

select @lockrecordid = inserted.uniqueid_c from inserted
select @lockcolumnvalue = echo.es_lockcolumns.value_vc from
echo.es_lockcolumns
select @lockcolumnrid = echo.es_lockcolumns.es_lockrecordsid_c from
echo.es_lockcolumns
select @status = inserted.status_c from inserted

--This sets the printed_c field to 'Y'when the record is locked
if @status in ('L', 'A')
begin

update ar.activwork
set printed_c = 'Y'
where @lockrecordid = @lockcolumnrid and
ar.activwork.uniqueid_c = @lockcolumnvalue
end
else

--This sets the printed_c field to 'N' when the record is unlocked
if @status = 'IS'
begin

update ar.activwork
set printed_c = 'N'
where @lockrecordid = @lockcolumnrid and
ar.activwork.uniqueid_c = @lockcolumnvalue
end

 

[mercwrought]

Imho your problem is in

...where @lockrecordid = @lockcolumnrid...

it just doesnt look right

 

[vongrunt]

Even if we ignore multirow update issues (common thing like air, water and @@IDENTITY ), this is weird at best:

select @lockcolumnvalue = echo.es_lockcolumns.value_vc from echo.es_lockcolumns

How many rows has table es_lockcolumns?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[MISdad]

There are no multirow update issues.

The table es_lockcolumns had a field called value_vc that is equal to the uniqueid_c field from the activwork table. There is another field in the es_lockcolumns table called es_lockrecordsid_c that is equal to the uniqueid_c field from the es_lockrecords table.

This makes a one to one to one relationship. There is one record in es_lockcolumns that has one corresponding record in activwork and one corresponding record in es_lockrecords.

Hope this helps clear things up a bit.

 

[mercwrought]

would it not be like this then

select @lockcolumnvalue = echo.es_lockcolumns.value_vc from
echo.es_lockcolumns where lockrecordsid = @lockrecordid

and this

select @lockcolumnrid = echo.es_lockcolumns.es_lockrecordsid_c from
echo.es_lockcolumns where lockrecordsid = @lockrecordid

or is there only one record in the lockrecords table?

 

[mercwrought]

with that said shouldent the rest be

if @status in ('L', 'A')
begin

   update ar.activwork
   set printed_c = 'Y'
   where ar.activwork.uniqueid_c = @lockcolumnvalue
end
else

--This sets the printed_c field to 'N' when the record is unlocked
if @status = 'IS'
begin

   update ar.activwork
   set printed_c = 'N'
   where ar.activwork.uniqueid_c = @lockcolumnvalue
end

? i dont see a use for the @lockcolumnrid varable.
please not that this is just my assement and im not perfect.

 

[MISdad]

No. There are multiple rows in all 3 of the tables.

I think I see what you're saying though. I'll try modifying the code and let you know how it goes. Thanks.

 

[MISdad]

mercwrought,

Thanks for your help. It took a while but what you were trying to point out finally clicked (no one ever said I was very bright). I was overcomplicating and thus confusing the trigger. I was trying to establish the link between the 3 tables throughout the entire trigger when all I really needed to do was establish 1 link in the select portion and the other link in the actual code that is being executed. Your suggestion on getting rid of the @lockcolumnrid variable was right on. It now works like a charm.

Thanks again,
Jim

 

[mercwrought]

it was no problem. I want to thank vongrunt, your post made it click in my mind what was happening.