Re: SE Trigger/Exception problem

ackerbau@us.ibm.com wrote: > > A couple of thoughts (that may or may not apply): > > 1. Can you share the code? Might make it easier to pinpoint the problem. > Sure, but it's nothing out of the ordinary I don't think. Trigger and SP at end of this message. > 2. Have you included the statements "set debug file to ... " and "trace on" in > the stored procedure, to see what happens when the trigger fires? > Not yet. I was still working on the 'Maybe this is normal' theory, which now seems to be confirmed. > 3. Are there any other actions to be taken as a result of this insert? If the > logical unit of work does not extend beyond the insert and its validation (your > trigger/stored procedure combo), what is the necessity of issuing an explicit > transaction? > No reason why an explicit transaction has to be issued. It's just that I'd always assumed that a triggers was a watertight basis for preventing non-privileged users of a database from inserting rows which result in a semantic inconsistency. I'm now trying to 're-educate' myself on this view, having considered Art's reply! > 4. Are you using global values? If so, are they necessary? In this case, it > sounds like the only data you should be feeding to the procedure is what is > passed from the trigger. Assuming you are comparing the value passed by the > trigger with some value you derive from the database, you shouldn't need your > variables to be defined as global. No global values. > > 5. Is this strictly a business rule violation? Or possibly a data integrity > issue? Meaning, is the data sound in and of itself? Should the record be > rejected in its own right without the insert trigger being present (i.e., null > values where data is required, lack of a corresponding foreign key, etc.)? > Hmm, maybe best if I explain the semantics so that you can form your own judgement. I'm using Informix for my own training/evaluation purposes. In this case I have database for classical music recordings. Now _that's_ original, I hear you say. But because it's classical music you get a nice many-to-many relationship between 'works' and 'disc=sets'. Most of my CDs conatain more than one work, and I have many works represented recordings on multiple disc-sets. (There's enough sophistication for me to query, for example, what I've got in my collection by American composers who are still living or died after 1945.) The trigger in question is on the 'recordings' table which has foreign keys into the 'discsets' and 'works' table, plus a column for duration. So a row says 'there is a recording of work X on discset Y, and it lasts for t'. But discsets also have a known total duration which is stored as a column in the 'discsets' table. My trigger is trying to reject an insert if it gives rise to the situation where the total duration of the recordings on a discset is greater than the total playing time of the discset. I think this falls into the category of 'business rule' violation, if such a beast can exist. <start code fragment> create procedure chk_duration(dsid integer) define tt interval hour to second; define dsdur interval hour to second; let tt = (select sum(duration) from recordings where discsetid=dsid); let dsdur = (select duration from discsets where id=dsid); if tt > dsdur then raise exception -746, 0, 'Attempt to exceed total discset duration'; end if; end procedure; create trigger ins_chk_duration insert on recordings referencing new as new_row for each row ( execute procedure chk_duration(new_row.discsetid) ); create trigger upd_chk_duration update of duration on recordings referencing new as new_row for each row ( execute procedure chk_duration(new_row.discsetid) ); <end code fragment> Cliff.