Re: Trigger does not seem to fire from front end or enterprise manager
From: Keith G Hicks (krh_at_comcast.net)
Date: 03/23/04
- Next message: Aaron Bertrand [MVP]: "Re: Return Key (identity) on INSERT operation (SQL/ASP)"
- Previous message: John Gilson: "Re: Need "On Change Of" aggregate"
- In reply to: Tibor Karaszi: "Re: Trigger does not seem to fire from front end or enterprise manager"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 23 Mar 2004 14:18:53 -0500
Thanks for your help. I've solved the problem in a rather gumpy way, but it
works. (see notes below)
"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:%239qxruPEEHA.240@tk2msftngp13.phx.gbl...
> Keith,
>
> First of all, there is no need to even do COMMIT in a trigger. If all is
OK,
> the transaction will be committed as the statement completes.
OK, makes sense.
> Also, I have a feeling that your trigger code doesn't handle multirow
> operations, but I can't say for sure. Just one thing to be careful about,
> that you work against the changes made buy the modification; i.e., work
> against the inserted and deleted tables inside your trigger code.
Yes, I am doing that.
> And, I would look over the datamodel. It seems like you have a repeating
> column (CustReate1, Custrate2 ...) which violates the first normal form.
> Having a non-normalized database generally means that you end up with
these
> kind of unpleasant things.
You're right, it's not perfectly normalized and I get really irritated at
non-normalized databass, but in this case it's the most practical way to
handle the table.
> I can't say for sure why it "work" when you change from QA but not from
EM,
> but again: run a profiler trace and you will see what SQL EM submits.
> And last, I suggest you post the trigger code and explains what it is
> supposed to do (along with DDL). Hopefully someone can come up with some
> good suggestions...
Got it to work in a round about way. :)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
>
> "Keith G Hicks" <krh@comcast.net> wrote in message
> news:OVHmCmPEEHA.3384@TK2MSFTNGP12.phx.gbl...
> > No, not a view.
> >
> > I think I've discovered that the problem has to do with the running of a
> > dynamic sql in the trigger.
> >
> > if I do this:
> >
> > Select @x = 1
> > While @x < 11
> > Begin
> > Select @sql = 'Select * From tblCustomers Where CustRate' + str(@x,
> 2,
> > 0) = @NewPrice
> > Exec(@sql)
> > If @@RowCount > 0
> > RollBack Transaction
> > else
> > Commit Transaction
> >
> > Select @x = @x + 1
> > End
> >
> > The above runs fine in the query analyzer but not in EM or in my MS
Access
> > front end.
> >
> > But if I do this:
> >
> > If Exists (Select * From tblCustomers Where CustRate1 = @NewPrice)
> > RollBack Transaction
> > else
> > Commit Transaction
> >
> > it works everywhere.
> >
> > The troube with the 2nd method of course is that I have to do it for all
> 10
> > CustRate fields.
> >
> > Any ideas?
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote
> in
> > message news:uMr7yRPEEHA.3424@tk2msftngp13.phx.gbl...
> > > No, that should not happen. Are you certain that it isn't a view you
are
> > > working against?
> > >
> > > To see what really happens, I suggest that you run a profiler trace to
> see
> > > what SQL EM submits.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > >
> > >
> > > "Keith G Hicks" <krh@comcast.net> wrote in message
> > > news:%23EwXwxOEEHA.3716@TK2MSFTNGP10.phx.gbl...
> > > > Please do not yell at me for not being more specific. I am having a
> > > problem
> > > > with a trigger and will get more specific if necessary.
> > > >
> > > > In general is there a reason why a trigger would fire properly when
> > doing
> > > an
> > > > update statement on a table from the query analyzer but seemingly
NOT
> > fire
> > > > at all when making a change to a field in the enterprise manager?
> > > >
> > > > Thanks,
> > > >
> > > > Keith
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: Aaron Bertrand [MVP]: "Re: Return Key (identity) on INSERT operation (SQL/ASP)"
- Previous message: John Gilson: "Re: Need "On Change Of" aggregate"
- In reply to: Tibor Karaszi: "Re: Trigger does not seem to fire from front end or enterprise manager"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
