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? |
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. |
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 @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...
(09 Dec '13, 03:49)
Volker Barth
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.
(09 Dec '13, 12:11)
Erik Anderson
|
Please show us the trigger code, and the table definitions.
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?
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.
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