Re: trigger question
Phuoc Ho wrote: > > In an effort to monitor dates entered into the database I wrote a > trigger and procedure to check and correct bad dates. I am > encountering error -747 (Table or column matches object referenced > in triggering statement). It did not make sense to me that you can > not modify the same field that the user is trying to insert or update > in a trigger. Can anyone please let me know if there is a way around > this problem. Phuoc, this is documented (I forget where): In the trigger action (or in a procedure that the trigger action calls) you cannot modify a column that tickled (OK, invoked) the trigger. This applies even on that column in a different row of the table. Grrr! Frustration!! This was imposed in order to prevent recursively called triggers, which could [conceivably] cause a stack overflow. Editorializing follows: I really wish they would remove this restriction. If they are worried about stack overflow, let them impose a limit on stack depth, as they do with cascading deletes (60 or so levels). Some databases are heavily dependent on the "parts explosion" design, which involves self-referential tables. Triggers become unwieldly in such situations; suddenly, everyone MUST use use an explict call to SPL for propagating a change. === END EDITORIAL === > thanks in advance. You're welcome, in hindsight. ;-) -- -- Jake (Low threshold of frustration) . . _..-'( )`-.._ ./'. '||\\\\. }\\_/{ .//||` .`\\. ./'.|'.'||||\\\\|.. )o o( ..|//||||`.`|.`\\. ./'..|'.|| |||||\\`````` \\'@'/ ''''''/||||| ||.`|..`\\. ./'.||'.|||| ||||||||||||. | .|||||||||||| ||||.`||.`\\. /'|||'.|||||| ||||||||||||{ | }|||||||||||| ||||||.`|||`\\ '.|||'.||||||| ||||||||||||{ | }|||||||||||| |||||||.`|||.` '.||| ||||||||| |/' ``\\||`` | ''||/'' `\\| ||||||||| |||.` |/' \\./' `\\./ \\!|\\ /|!/ \\./' `\\./ `\\| V V V }' `\\ /' `{ V V V \\ \\ \\ V / / / +-----------------------------------------------------------+ | Impeccable Logic: A thought process which successfully | | resists chicken bites | +-----------------------------------------------------------+