replace value in trigger

replace value in trigger

Post by Dan Guzma » Fri, 01 Dec 2000 04:00:00



This behavior depends on the setting of the 'recursive triggers' database
option.  This is off by default which means that a trigger will not execute
itself directly.  The example I posted will provide the desired results with
that database option off.

If you have 'recursive triggers' turned on, the example will not work
properly because it will execute itself until the nesting level is exceeded.
If that is your case, you should consider a computed column or view instead.

Please don't post the same question to multiple newsgroups.  This tends to
result in redundant and inefficient efforts on the part of responders.

Hope this helps.


> But if there is trigger for update it would run. And go on, go on....




> > It's unclear from your post what the requirements are calculating the
second
> > value.  Assuming that column 'b' is calculated as 10 raised to the power
> > specified in column 'b', try:

> > CREATE TRIGGER aaaa_Trigger
> >  ON aaaa
> >  FOR INSERT, UPDATE
> > AS
> > UPDATE aaaa
> > SET b = POWER(10, i.b)
> > FROM aaaa
> > JOIN inserted i ON
> >     i.PKColumn = aaaa.PKColumn

> > Note that you will need to join on a unique  not null column (a.k.a.
primary
> > key).  You may also want to consider a computed column or view instead,

> > Hope this helps.

> > -----------------------
> > SQL FAQ links (courtesy  Neil Pike):

> >  http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
> >  (faqxxx.zip in lib 7)
> >  or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> >  or www.sqlserverfaq.com
> >  or www.mssqlserver.com/faq
> > -----------------------



> > > There is a trigger for insert. And I have to change value for a column
in
> > this trigger.
> > > How can I do it
> > > ===============
> > > INSERT INTO aaaa (a, b) VALUES (1,2)
> > > -- the result of 'SELECT a, b FROM aaa' must be
> > > 1,  100 -- not 1,2
> > > ===============

> > > Thanks