Trigger Trouble
-
I have a trigger and it fires properly whenever I update two fields within the table. It does not fire however when I insert a new record into the table. I have listed the trigger and the Insert statement below:
Am I missing something?
---------------------------------------------
Here's the trigger:
CREATE TRIGGER trig_GetCustomerInfo ON dbo.tblMembers
FOR INSERT, UPDATE
AS
exec stProc_GetCustomerInfo
---------------------------------------------
---------------------------------------------
Here's the insert statement:
Insert into tblMembers (
custid,
firstname,
middle,
lastname,
address1,
address2,
city,
state,
zip,
pphone,
sphone,
email,
ovid,
boflag,
new,
course
)
VALUES
('zzzzzzzz9999',
'Gary',
'G',
'Gourmet',
'12345 main st',
'98765 main st.',
'Cranston',
'RI',
'02920',
'1234567890',
'5555555555',
'user@127.0.0.1',
'GAAA10121138',
0,
1,
'0203-0245')
---------------------------------------------
Thanks for any help,
Scott
-
Scott,
Have you considered putting some kind of tracer in the beginning of the trigger, before the exec statement, just to make sure that the trigger is not firing? You could insert a row into a log table, issue xp_logevent to put a message into the Windows Application Event Log.*
Let us know if that helps, and if not, post the table definition also (scripted from SQL EM would be best) and I'll see if I can replicate the problem. It would be good in that case to post the output from SELECT @@VERSION also.
Good luck,
Chris
* The Windows Application Event Log is also know as the Windows Application Application Log, but only on exactly one Web site.
-
Thanks, I'll post the info on Tuesday.
Scott
-
The trigger gets fired but it does not execute the stored procedure within the trigger. It only executes the stored procedure when there is an update on the table. Any ideas? Thanks, Scott
-
You may even want to break them out separately -- one for update and one for insert.
-
I separated the triggers and there is still no luck. Please help, Scott
-
Do stored procedures called by a trigger get syntax checked before execution? In the stored procedure that gets called there is a cursor that queries for the inserted records within the table... I'm thinking that maybe the records are not in the table yet because the trigger is being executed? I don't know, but all I know is that I made another stored procedure that prints a text message and that one was fired during the trigger and the print command at the beginning of the stored procedure with the cursor in it did not get fired.
-
Scott,
Can you post DDL for ... hmm ... the stProc_GetCustomerInfo proc, plus all the tables involved (tables affected by both the insert and the proc)? Or would that be gobs of code?
Also, when you say that the trigger fires but does not execute the procedure - you might have already done this, but did you put tracers inside the procedure also to make sure it's not being executed? If the trigger you have posted is indeed the full trigger definition, it doesn't seem likely that the procedure isn't executing at all, since the procedure call is the only thing in the trigger (except for the tracers you put in). Is that really what you've found out? That would be odd indeed.
Chris
-
A few dozen print statements later... It turned out that a where clause was incorrect, but at least I learned how the inserted and deleted tables work during a trigger. Thanks for all the help, Scott
-
Very good - I'm glad you got it figured out!
Chris
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply