INSTEAD OF UPDATE trigger not finding rows

[Former Member]

I've spent about half a day on this so far, I'm hoping someone here can give me some enlightenment on where to go next. This is my first attempt at an instead-of trigger. The view involves a join between two tables and a GROUP BY.

Symptoms:

select count(*) from theView

returns 275 rows

update theView set someCol = 3

returns 109 rows updated

select * from theView where id=2013713

returns 1 row

update theView set someCol = 3 where id = 2013713

returns 0 rows updated. MESSAGE statements on the triggers confirm that no triggers are firing.

a request trace of the previous update returns:

=,<,12696,EXEC,917941
+4,P,12696,UPDATE Work[ GrByH[ HF[ bms<PedId> ] *JHx pp<seq> ] ]
=,W,12696,100,Row not found
=,>,12696,EXEC

A graphical plan of the previous update returns:

( In case I can't get this to appear: http://static.teamworkgroup.com/plan6.png )

I'm seeing on this chart that the [DT] node returns exactly 1 row (the row I wish to update). The <Work> node has zero rows. I do not know what action would cause a work table to filter the results of the underlying DT.

So, any ideas on where to go next with this?

[Breck_Carter]

Please show us the trigger code, and the table definitions.

[Former Member]

Thank you for you response. I do recognize that it is difficult or impossible to do an in-depth analysis without an isolated example, which takes about half a day to do. Before I get something like that completed (after I review any recent EBFs), are there any known broken assumptions in how "in place" triggers work? Are there places where an UPDATE of rows returned from a view would not cause any rows to change?

[Former Member]

Okay, this is starting to get redicuous, new_name is filled with garbage data (strings are things like char(1), integers like 65537, and doubles are like 7.746824126E-304). I'm obviously doing something unexpected. I'll try to switch to statement triggers and see if that has any effect.

[Former Member]

Okay, here is a repro I threw together, obviously I'm failing an assumption check somewhere: http://pastebin.com/RWJ4JKGc

if you do an "update dba.InPlaceTestView set seq2=5" the trigger will report the contents of new_name.

For me it returns "GroupID=65537, type= , row_id=, seq1=3.237958023E-319, seq2=5, seq3=6.365990627E-313"

Tested with: engine=12.0.1.3942, dbinit=11.0.1.2436

Tested with: engine=12.0.1.3942, dbinit=12.0.1.3942

[Former Member · Accepted solution]

I now have a possible explanation and/or workaround to this issue. The kind of symptoms I'm seeing likely mean that this should become an engineering case in the near future (if it hasn't already been corrected in some EBF). An EBF was never in the timeline for me resolving this issue though; this solution should be good for me.

The following code does not work:

create table dba.InPlaceTest
(
    ID integer not null primary key default autoincrement,
    Val double,
    GroupID integer not null,
    Type varchar(5) not null,
    seq integer not null,
    unique(GroupID, Type, seq)
);
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(15673, 'STD', 1, 20);
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(15673, 'STD', 2, 40);
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(15673, 'STD', 3, 60);
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(18635, 'STD', 1, 10);
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(18635, 'STD', 2, 30);
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(18635, 'STD', 3, 50);

create or replace view dba.InPlaceTestView as
select GroupId, type, GroupId || '-' || type as row_id,
    sum(if seq=1 then Val else 0 endif) as Seq1,
    sum(if seq=2 then Val else 0 endif) as Seq2,
    sum(if seq=3 then Val else 0 endif) as Seq3
from dba.InPlaceTest
group by GroupId, type;

create or replace trigger InPlaceTestView_Update instead of update on dba.InPlaceTestView
    referencing old as old_name new as new_name for each row
begin
    raiserror 99999 'row: GroupID=' || new_name.GroupID || ', type=' || new_name.type || ',     row_id=' || new_name.row_id
        || ', seq1=' || new_name.seq1 || ', seq2=' || new_name.seq2 || ', seq3=' || new_name.seq3;
end;

If you do an "update dba.InPlaceTestView set seq2=5" the trigger will report the contents of new_name.

For me it returns "GroupID=65537, type= , row_id=, seq1=3.237958023E-319, seq2=5, seq3=6.365990627E-313"

Building the view so that the GROUP BY is not in the outer shell resolves this issue:

create table dba.InPlaceTestPK
(
    GroupID integer not null,
    Type varchar(5) not null,
    ID integer not null unique default autoincrement,
    primary key(GroupID, Type)
);

//drop table dba.InPlaceTest;
create table dba.InPlaceTest
(
    ID integer not null primary key default autoincrement,
    Val double,
    GroupID integer not null,
    Type varchar(5) not null,
    seq integer not null,
    unique(GroupID, Type, seq)
);
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(15673, 'STD', 1, 20);
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(15673, 'STD', 2, 40);
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(15673, 'STD', 3, 60);
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(18635, 'STD', 1, 10);
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(18635, 'STD', 2, 30);
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(18635, 'STD', 3, 50);

insert into dba.InPlaceTestPK(GroupID, Type) select distinct GroupID, Type from dba.InPlaceTest;

create or replace view dba.InPlaceTestView as
with data as (
    select GroupId, type,
        sum(if seq=1 then Val else 0 endif) as Seq1,
        sum(if seq=2 then Val else 0 endif) as Seq2,
        sum(if seq=3 then Val else 0 endif) as Seq3
    from dba.InPlaceTest
    group by GroupId, type
)
select InPlaceTestPK.GroupId, InPlaceTestPK.type, InPlaceTestPK.ID as row_id,
    data.Seq1, data.Seq2, data.Seq3
from dba.InPlaceTestPK
    join data on InPlaceTestPK.GroupID=data.GroupID and InPlaceTestPK.Type=data.Type;

create or replace trigger InPlaceTestView_Update instead of update on dba.InPlaceTestView
    referencing old as old_name new as new_name for each row
begin
    raiserror 99999 'row: GroupID=' || new_name.GroupID || ', type=' || new_name.type || ', row_id=' || new_name.row_id
        || ', seq1=' || new_name.seq1 || ', seq2=' || new_name.seq2 || ', seq3=' || new_name.seq3;
end;

If you do an "update dba.InPlaceTestView set seq2=5" the trigger will report the contents of new_name.

For me it returns "GroupID=15673, type=STD, row_id=1, seq1=20, seq2=5, seq3=60"

I have also made initial confirmations that the triggers are now firing on all rows, which was the original symptom.

[ximen]

You did not provide relevant information about demand: you want to achieve what is the result of two tables GOUP?? what DT table structure, The view should be cannot be updated you should update the table

this code for example

ALTER TRIGGER "tr_A_in" after insert order 2 on
dba.KARL
referencing new as new_KARL
for each row
begin
case when (isnull(new_KARL.user_10,0)<= 0 and new_KARL.product_code='XQ') then 
   Raiserror 30002 'is not null';
when (isnull(new_KARL.user_4,0) <= 0 and new_KARL.location_id='CP')  then
   Raiserror 30002 'user_4 is not null';
when (new_KARL.source='F' and isnull(new_KARL.dept_id,'')='') then 
   Raiserror 30002 'dept_id is not null';
else 
  end case;

end

[VolkerBarth]

@mfkpie8: It seems that you have not realized the actual question: It's not about triggers per se, it's about an INSTEAD OF trigger, a particular form of triggers to allow updates on otherwise non-updatable views...

[Former Member]

Exactly. Here's a link with most of the documentation I've found for it: http://dcx.sybase.com/1200/en/dbusage/proctrig-b-4949515.html

As far as I can tell an update of a view causes a SELECT with the specified WHERE clause, followed by calling the INSTEAD OF trigger on every row returned. Except that it's ignoring about half the returned rows without explanation.