"Before Update" and "After Update Of" triggers

Hi all,

In a row-level before update trigger I set the value
of a column ("SomeColumn) to some value (the value is only
changed by this trigger, not by the update statement itself)

Now I notice that the row-level trigger defined
as "after update of SomeColumn" doesn't fire
when I change the value of this column in the
before update trigger. It does fire (as expected) when
the change comes from the update statement itself)

Questions:
 - Is this expected behaviour?
 - how can I uniformly trigger a change of a column
   wether set by the update statement or by a before
   update of column trigger?

Using SQLAnywhere 7.02.1402

Thanks in advance
and best regards,

Ton van den Broek


0
Ton
9/9/2002 9:04:00 AM
sybase.sqlanywhere.general 32637 articles. 22 followers. Follow

5 Replies
1228 Views

Similar Articles

[PageSpeed] 18
Get it on Google Play
Get it on Apple App Store

If the column in question is not set by the *original*
UPDATE statement then that may be normal.

A "SET" statement in a BEFORE trigger is
not the same thing as an UPDATE operation.
While it does change a column value, it is
not implemented as an update in the same
strict sense.

For example SET statements executed in an AFTER
trigger must be implemented as an UPDATE and
they will cause additional 'update' triggers executions.
This is one thing that is not true with SET statements
in the BEFORE trigger case (after triggers are not
fired).

"Ton van den Broek" <ton@tvdb.nl> wrote in message
news:RtM78J#VCHA.74@forums.sybase.com...
> Hi all,
>
> In a row-level before update trigger I set the value
> of a column ("SomeColumn) to some value (the value is only
> changed by this trigger, not by the update statement itself)
>
> Now I notice that the row-level trigger defined
> as "after update of SomeColumn" doesn't fire
> when I change the value of this column in the
> before update trigger. It does fire (as expected) when
> the change comes from the update statement itself)
>
> Questions:
>  - Is this expected behaviour?
>  - how can I uniformly trigger a change of a column
>    wether set by the update statement or by a before
>    update of column trigger?
>
> Using SQLAnywhere 7.02.1402
>
> Thanks in advance
> and best regards,
>
> Ton van den Broek
>
>


0
Nick
9/9/2002 2:46:27 PM
Nick,
> If the column in question is not set by the *original*
> UPDATE statement then that may be normal.
>
> A "SET" statement in a BEFORE trigger is
> not the same thing as an UPDATE operation.

I already was afraid of this ... Strange thing is
that when I change "after update of" to an
"after update" trigger, the old and new values
are correct (and different from each other), so
I'm curious to what values the "update of" type
of trigger uses (or maybe what the timing of the
snapping of values is)

Problem I have is that there tons of these after
"update of" type of triggers. So for performance
reasons ("after update" triggers always fire),I probably
have to test for value changes myself for all columns
involved in the "of-clause" ....

Should I best rewrite all triggers like the following ?:

if isnull(o.col1,0)<>isnull(n.col1,0) or
isnull(o.col2,'')<>isnull(n.col2,'') ... then
  ... original body ...
end if

Or is there anyony more experience who has
a better suggestion?

Thanks in advance
and best regards,

Ton van den Broek


0
Ton
9/9/2002 5:03:29 PM
I do want to be clear on one point here:

    We are not talking about the case where
    the update statement is also setting that
    column.

If you have a problem there let us know because
you would have a different problem.

Anyway . . .

You probably have a few implied, simultaneous criteria there
that this suggestion  won't answer, but here it is::

    It may be that all you need to do here is to [selectively |
    conditionally] substitute an UPDATE statement in place
    of that SET statement setting just the specific column(s)).
    That way the after-update-of-columns trigger will fire.
    If the number of trigger code changes you need to make
    (and the volume of transactions affected) are few, this
    may be entirely acceptable. Since triggers are pretty
    optimal the performance impact of this design change
    could be small (unless you introduce a lot of  recursion).

From a performance standpoint, handling everything in the
before trigger, may make sense (but how much you are going
to save will need to be measured).  The problem with this
approach in the code is that you now need to handle two
simlutaneous criteria:

    - the case where you were originally setting that value
    - the case where the outer operation (update statement)
      is setting it

Your suggested checks handle the latter case but your
existing after update-of-column trigger could also be left
to fill in function too.

From the modular programming perspective, leaving you
existing triggers in place may cause *duplicate* logic
but only the control  aspect needs to be duplicated;
computations can be modularized into a user defined
function (if you logic for this case is complex).


"Ton van den Broek" <ton@tvdb.nl> wrote in message
news:xpL#cVCWCHA.81@forums.sybase.com...
> Nick,
> > If the column in question is not set by the *original*
> > UPDATE statement then that may be normal.
> >
> > A "SET" statement in a BEFORE trigger is
> > not the same thing as an UPDATE operation.
>
> I already was afraid of this ... Strange thing is
> that when I change "after update of" to an
> "after update" trigger, the old and new values
> are correct (and different from each other), so
> I'm curious to what values the "update of" type
> of trigger uses (or maybe what the timing of the
> snapping of values is)
>
> Problem I have is that there tons of these after
> "update of" type of triggers. So for performance
> reasons ("after update" triggers always fire),I probably
> have to test for value changes myself for all columns
> involved in the "of-clause" ....
>
> Should I best rewrite all triggers like the following ?:
>
> if isnull(o.col1,0)<>isnull(n.col1,0) or
> isnull(o.col2,'')<>isnull(n.col2,'') ... then
>   ... original body ...
> end if
>
> Or is there anyony more experience who has
> a better suggestion?
>
> Thanks in advance
> and best regards,
>
> Ton van den Broek
>
>


0
Nick
9/10/2002 3:40:11 PM
Nick,

I really appreciate your insights and hope
you care to read this reply, because I might
be missing something (as I usually do <g>)

> I do want to be clear on one point here:
>
>     We are not talking about the case where
>     the update statement is also setting that
>     column.

It depends (as always <g>). Let me describe a little more
about the scenerio I'm dealing with:

I've this one column (Quantity) on a sales table, that can
be changed from the update statement and/or be SET from
within the before update statement. Depending on some logic,
the before trigger -can- set this single column (even overruling
the original change incurred by the update stament).

There are several reasons (I think <g>) this needs to be done
in the before trigger:
this is in a replication environment, where the (heavy) logic (in the
before event) is only done once (at the site that incurs the update)
and doesn't have to be executed by dbremote on all sites as only
the actual value set/updated is replicated

There already are quite a of (after update of ) triggers that
always have to fire whenever this column is changed (either
consolidated and remotely).

The problems I run into are these:
- By -only- SETting the value in the before trigger the after update
  "OF" triggers don't fire
- SETting the value in an AFTER trigger is not allowed
- UPDATING the value in an after trigger would be inefficient
  and introduce recursion
- using an update stament in the before trigger results in
  an endlessly firing of this before trigger (recursion)

I'd like to stay away from recursion for simplicity reasons
(I mostly only use this for a "divide and conquer" solution,
while in my scenerio the problem seems quit "lineair") and
I preferrably do this new logic in the before event, without
having to change lots of "after update of" triggers (which
all have to be changed in passthrough mode ...).
But I guess I want too much with too little effort <g> ....

The funny thing is that apparently the value changes
in an "of"-clause of an AFTER update trigger are
evaluated BEFORE the firing of before update triggers,
while I expected the mechanism to work like this:
snap values (old), perform before update triggers,
perform the actual update / including SET changes,
snap values (new) and then perform "after update of"
triggers based on changed old-new values.
But this probably only show my lack of insight in the
trigger mechanism

So to come to an end: I probably stick the changing
the "after update of" triggers to "after update" and
test for the changes myself, which seems to work
and keep the design simple, unless someone could
provide me a better solution.

Best regards,
Ton

> Anyway . . .
>
> You probably have a few implied, simultaneous criteria there
> that this suggestion  won't answer, but here it is::
>
>     It may be that all you need to do here is to [selectively |
>     conditionally] substitute an UPDATE statement in place
>     of that SET statement setting just the specific column(s)).
>     That way the after-update-of-columns trigger will fire.
>     If the number of trigger code changes you need to make
>     (and the volume of transactions affected) are few, this
>     may be entirely acceptable. Since triggers are pretty
>     optimal the performance impact of this design change
>     could be small (unless you introduce a lot of  recursion).
>
> From a performance standpoint, handling everything in the
> before trigger, may make sense (but how much you are going
> to save will need to be measured).  The problem with this
> approach in the code is that you now need to handle two
> simlutaneous criteria:
>
>     - the case where you were originally setting that value
>     - the case where the outer operation (update statement)
>       is setting it
>
> Your suggested checks handle the latter case but your
> existing after update-of-column trigger could also be left
> to fill in function too.
>
> From the modular programming perspective, leaving you
> existing triggers in place may cause *duplicate* logic
> but only the control  aspect needs to be duplicated;
> computations can be modularized into a user defined
> function (if you logic for this case is complex).
>
>
> "Ton van den Broek" <ton@tvdb.nl> wrote in message
> news:xpL#cVCWCHA.81@forums.sybase.com...
> > Nick,
> > > If the column in question is not set by the *original*
> > > UPDATE statement then that may be normal.
> > >
> > > A "SET" statement in a BEFORE trigger is
> > > not the same thing as an UPDATE operation.
> >
> > I already was afraid of this ... Strange thing is
> > that when I change "after update of" to an
> > "after update" trigger, the old and new values
> > are correct (and different from each other), so
> > I'm curious to what values the "update of" type
> > of trigger uses (or maybe what the timing of the
> > snapping of values is)
> >
> > Problem I have is that there tons of these after
> > "update of" type of triggers. So for performance
> > reasons ("after update" triggers always fire),I probably
> > have to test for value changes myself for all columns
> > involved in the "of-clause" ....
> >
> > Should I best rewrite all triggers like the following ?:
> >
> > if isnull(o.col1,0)<>isnull(n.col1,0) or
> > isnull(o.col2,'')<>isnull(n.col2,'') ... then
> >   ... original body ...
> > end if
> >
> > Or is there anyony more experience who has
> > a better suggestion?
> >
> > Thanks in advance
> > and best regards,
> >
> > Ton van den Broek
> >
> >
>
>


0
Ton
9/11/2002 11:54:47 AM
Okay that is pretty clear.

Just so you know I agree with you about the cost
and usefullness of recursion. Recurssion is a useful
programming technique and permitted in ASA triggers
but for most designs it should be avoided.  But if
it solves an otherwise difficult design problem, it
is available.

In your case you are probably safer to switch over to
the general after update trigger form.

A couple of observations:

> - using an update stament in the before trigger results in
>   an endlessly firing of this before trigger (recursion)

If an UPDATE is substituted for the SET statement in the
before trigger then unbounded recursion will happen but
that can easily be handled by a simple test for the value
being set here.   So an extra level of before triggers will fire,
in the general case, but then you can gain back the
'after update of' triggers by the addition of a single bit of

    IF !=  then update

logic.




"Ton van den Broek" <ton@tvdb.nl> wrote in message
news:TfHxXyYWCHA.292@forums.sybase.com...
> Nick,
>
> I really appreciate your insights and hope
> you care to read this reply, because I might
> be missing something (as I usually do <g>)
>
> > I do want to be clear on one point here:
> >
> >     We are not talking about the case where
> >     the update statement is also setting that
> >     column.
>
> It depends (as always <g>). Let me describe a little more
> about the scenerio I'm dealing with:
>
> I've this one column (Quantity) on a sales table, that can
> be changed from the update statement and/or be SET from
> within the before update statement. Depending on some logic,
> the before trigger -can- set this single column (even overruling
> the original change incurred by the update stament).
>
> There are several reasons (I think <g>) this needs to be done
> in the before trigger:
> this is in a replication environment, where the (heavy) logic (in the
> before event) is only done once (at the site that incurs the update)
> and doesn't have to be executed by dbremote on all sites as only
> the actual value set/updated is replicated
>
> There already are quite a of (after update of ) triggers that
> always have to fire whenever this column is changed (either
> consolidated and remotely).
>
> The problems I run into are these:
> - By -only- SETting the value in the before trigger the after update
>   "OF" triggers don't fire
> - SETting the value in an AFTER trigger is not allowed
> - UPDATING the value in an after trigger would be inefficient
>   and introduce recursion
> - using an update stament in the before trigger results in
>   an endlessly firing of this before trigger (recursion)
>
> I'd like to stay away from recursion for simplicity reasons
> (I mostly only use this for a "divide and conquer" solution,
> while in my scenerio the problem seems quit "lineair") and
> I preferrably do this new logic in the before event, without
> having to change lots of "after update of" triggers (which
> all have to be changed in passthrough mode ...).
> But I guess I want too much with too little effort <g> ....
>
> The funny thing is that apparently the value changes
> in an "of"-clause of an AFTER update trigger are
> evaluated BEFORE the firing of before update triggers,
> while I expected the mechanism to work like this:
> snap values (old), perform before update triggers,
> perform the actual update / including SET changes,
> snap values (new) and then perform "after update of"
> triggers based on changed old-new values.
> But this probably only show my lack of insight in the
> trigger mechanism
>
> So to come to an end: I probably stick the changing
> the "after update of" triggers to "after update" and
> test for the changes myself, which seems to work
> and keep the design simple, unless someone could
> provide me a better solution.
>
> Best regards,
> Ton
>
> > Anyway . . .
> >
> > You probably have a few implied, simultaneous criteria there
> > that this suggestion  won't answer, but here it is::
> >
> >     It may be that all you need to do here is to [selectively |
> >     conditionally] substitute an UPDATE statement in place
> >     of that SET statement setting just the specific column(s)).
> >     That way the after-update-of-columns trigger will fire.
> >     If the number of trigger code changes you need to make
> >     (and the volume of transactions affected) are few, this
> >     may be entirely acceptable. Since triggers are pretty
> >     optimal the performance impact of this design change
> >     could be small (unless you introduce a lot of  recursion).
> >
> > From a performance standpoint, handling everything in the
> > before trigger, may make sense (but how much you are going
> > to save will need to be measured).  The problem with this
> > approach in the code is that you now need to handle two
> > simlutaneous criteria:
> >
> >     - the case where you were originally setting that value
> >     - the case where the outer operation (update statement)
> >       is setting it
> >
> > Your suggested checks handle the latter case but your
> > existing after update-of-column trigger could also be left
> > to fill in function too.
> >
> > From the modular programming perspective, leaving you
> > existing triggers in place may cause *duplicate* logic
> > but only the control  aspect needs to be duplicated;
> > computations can be modularized into a user defined
> > function (if you logic for this case is complex).
> >
> >
> > "Ton van den Broek" <ton@tvdb.nl> wrote in message
> > news:xpL#cVCWCHA.81@forums.sybase.com...
> > > Nick,
> > > > If the column in question is not set by the *original*
> > > > UPDATE statement then that may be normal.
> > > >
> > > > A "SET" statement in a BEFORE trigger is
> > > > not the same thing as an UPDATE operation.
> > >
> > > I already was afraid of this ... Strange thing is
> > > that when I change "after update of" to an
> > > "after update" trigger, the old and new values
> > > are correct (and different from each other), so
> > > I'm curious to what values the "update of" type
> > > of trigger uses (or maybe what the timing of the
> > > snapping of values is)
> > >
> > > Problem I have is that there tons of these after
> > > "update of" type of triggers. So for performance
> > > reasons ("after update" triggers always fire),I probably
> > > have to test for value changes myself for all columns
> > > involved in the "of-clause" ....
> > >
> > > Should I best rewrite all triggers like the following ?:
> > >
> > > if isnull(o.col1,0)<>isnull(n.col1,0) or
> > > isnull(o.col2,'')<>isnull(n.col2,'') ... then
> > >   ... original body ...
> > > end if
> > >
> > > Or is there anyony more experience who has
> > > a better suggestion?
> > >
> > > Thanks in advance
> > > and best regards,
> > >
> > > Ton van den Broek
> > >
> > >
> >
> >
>
>


0
Nick
9/13/2002 6:00:09 AM
Reply:

Similar Artilces:

No "Update" or "Delete"!
Hi,I am using the dataset wizard to connect to a SQL database. The wizard creates my dataset and generates  "Select" and "Insert" statements but no "Update" or "delete". What can be the reason? By the way, this happens only on certain tables and not on others. I just can't figure out what the difference between these tables is. And, they all have a primary key. Actually I found this error before, running the configuration for the table again gives me that, sometimes I had to delete the table and start again. Hope this helps, let me...

Precedence of "where" ("of", "is", "will")?
Nobody on #perl6 today could answer this one. Is: Str | Int where { $_ } the same as: (Str | Int) where { $_ } or: Str | (Int where { $_ }) ? Followup questions, Mr. President: What kind of operators are "where", "of", "is", and "will"? Is there a reason that S03 doesn't list them? What are their precedence(s)? -- Chip Salzenberg - a.k.a. - <chip@pobox.com> Open Source is not an excuse to write fun code then leave the actual work to others. Chip Salzenberg writes: &...

.ALLCOL("%COLUMN%", " ", ", ", ", ")
Do you know anyway for me to exclude a subset of columns returned by this function. We have two columns (rec_user and rec_datetime) which are in all of our tables, but when generating triggers I want automatically generate a script which does not include those two columns but does include all other columns in that table. Bruce I should add that I am using PD 9.0.0.580. Bruce "Bruce Lamb" <lamb.bruce@mayo.edu> wrote in message news:6HgI315nCHA.155@forums.sybase.com... > Do you know anyway for me to exclude a subset of columns returned by this > function. ...

Proposal: Rename "software update" to "application update"
Rumor has it that lots of extension update bugs get filed in the "software update" component when they should be filed in the "extension/theme manager" component. How about renaming "software update" to "application update" so the scope is more clear? ...

"Update" and "Upgrade"
Hi, The last update of main.lang has these two lines: ;Upgrade now ;Update now I noticed that es-AR[1], de[2] and it[3] use the same translation for both lines. And a couple of lines before those, in the original main.lang you can read: .... Get your *update* to stay safe! <br/> <a href="%s">Download the free *upgrade* ... If they are used indistinctly both in the source and in the translations maybe they are to be dealt with (from a Mozilla point of view) as synonyms. The question, is it ok to do so? Is it important for the project/users to...

quotes, quotes, quotes...
I am getting this error and I know what is causing it, but I have no idea how to fix it, any help would be great. The script steps through the /var/log/messages file on a linux server and puts The entries into a mysql database. However when it gets to the 'hlt' line in the messages file it just barfs. The single quotes are freaking it out. I know about quotes but not how to use in this situation. Thanks, Paul Error: May 27 17:53:00 localhost kernel: Checking 'hlt' instruction... OK. <----- doesn't like this in the messages file DBD::mysql::st exec...

no "enable editing" "enable deleting" "enable updating"
In my visual studio 2005 beta1,there is no "enable editing", "enable deleting", "enable updating"options. why? is the vision wrong??thanks! Which control are you talking about?/Fredrik Normén - fredrikn @ twitterMicrosoft MVP, MCSD, MCAD, MCTASPInsidersMy Blog "gridview" control!! I have, so you should also have it if you create a data source for the GridView control in the design view and make sure you have specified the Insert and Update Command for the DataSource. You can also add a CommandField to the GridView columns <asp:CommandFi...

replace the "." with a ","
Oi.... I need to build a small programm in ASP.NET and chose to use C# for it.Now i got everything working but there's one little problem.the first textbox is a double. I need to make it so that when someone enters a "." then it gets replaced by a ","any ideas?Ghan  string blah = "4.2.2.2";blah = blah.Replace(".", ",");Ryan Ryan OlshanASPInsider | Microsoft MVP, ASP.NEThttp://ryanolshan.comHow to ask a question...

double quote
hello there...  i tried everything of think but not working the way i wanted to be... not sure what i'm missing...i'm generating a <span> in code behind and then using in javascript.... here is what i'm doing code behind: int i=0string _keywordID = "keyword";string _name = row["visit_info_nm"].ToString().Trim(); String _getElementByID = String.Format("<span id='{0}' OnClick = \"document.getElementById('{1}').value='{2}';\">{3}</span><br>", i, _keywordID, _name, _name); here is what it generate : <span id='1' OnClick = \"document.getElementById('keyword')...

"-" not "_"
I wrote a SQL statement in the data tab. I wrote a bunch of alaises as example ' word-type ' but when I hit the layout tab it converts the "-" to "_". So now my field name is ' word_type '. Is there any way to prevent this? CardGunner Don' use a hypen ( - ).  It isn't a valid character for column names.   See http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1188931,00.html   Here's an excerpt about column names: Letters as defined in the Unicode Standard 2.0 Decimal numbers from either B...

"Using" or "With"
Hi all Please can someone enlighten to me as regards the difference with the "Using" and "With" statement when accessing data - which is better, what are the limitations and/or any pointers. Many thanks. Regards DaveDavid WinchesterPlease mark as answer if this is the solution.  using gives you the ability to use the connection and it closes the connection directlly after you finish using it. and there is no need to try- cach - finaly. there is no limitation on using USING keywordMuhanad YOUNISMCSD.NETMy Blog || My Photos || LinkedIn I have a dataobject the re...

Using "+" or "||"
Using SQLAnywhere 5.5.04, I've gotten into the habit of using "||" in ISQL to indicate a string concatenation. I needed to paste my SQL statement into the PowerBuilder script painter for some embedded SQL, and PB didn't like the "||" very much at all. I changed it to "+" and it seems to be ok. Do these two operators indicate ~exactly~ the same thing? moin, afaik these two's are not the same! if you're using "||" and any term is NULL then in the resultstring the term will be ignored if you use "+" then the resu...

"To" and "From" missing
When I print emails, the words "To" and "From" are blank, even though the "To" name and "From name (addresser, addressee) do show up. This is not a problem for other users on my system. Suggestions In mailbox right click, view. On the message window, right click and choose print options. Make sure print header is checked. -- Barry Merchant NSC Volunteer SysOp *** no email unless requested please!! *** > In mailbox right click, view. On the message window, right click and > choose print options. Make sure prin...

Replacing "\\" with "\"
Hi all I'm getting this value from a CheckBoxList control - a location of file, i have to remove "\\" and replace it with "\" and pass it to Query, how to do it, i tried with Replace, but coud'nt suceed. "\\\\Blaze10xp\\BLZ_SFS_07\\Sample Excel Files\\Excel Files\\report2.xls" thank's in advance - Prakash.C you tried Replace like this? string newstring = oldstring.Replace(@"\\",@"\");Plese, do not forget to click "Mark as Answer" on the post that helped you. Thanx!My blog: Scenes From A Developer Memory yes i tr...

Web resources about - "Before Update" and "After Update Of" triggers - sybase.sqlanywhere.general

Resources last updated: 2/12/2016 1:23:12 PM