Trigger not firing
-
I have an AFTER trigger on a table. When a row is inserted into the table, the trigger is supposed to insert a new row into another table in another database. The trigger fires just fine in the development database server. The trigger does not fire properly in the production database server.
Both servers have SQL Server 2000 SP4. Not only does the trigger not fire, but it prevents the row from being inserted into the first table. When I disable the trigger, the row gets inserted into the first table like it should.
The row is being inserted into the first table via a stored procedure. When the trigger is enabled, I can type a new row into the table manually and the trigger fires as it should.
I actually have two separate insert triggers. One inserts a new row into database A, and the other inserts a new row into database B.
Any suggestions or ideas will be greatly appreciated.
Thanks,
Kathy Davis
-
Can you post the code of the failing/succeeding triggers?
-
CREATE TRIGGER insertMedPerson_T
ON SWCSPersonnel.dbo.PERSON_T
FOR INSERT
AS
INSERT INTO SWCSMedical.dbo.MED_PERSON_T (SSN)
SELECT SSN FROM INSERTED
CREATE TRIGGER insertStudent_T
ON SWCSPersonnel.dbo.PERSON_T
FOR INSERT
AS
INSERT INTO SWCSStuMgt.dbo.STUDENT_T (SSN)
SELECT SSN FROM INSERTED
-
This is probably just my preference, but I think I would combine these two INSERTs into a single trigger.
Are you receiving any error messages? Try performing a simple INSERT through QA - you might get better diagnostics than you would through other means. If that succeeds, it might be time to look at your stored proc ...
Regards, Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Which one is failing?
What do you get when you run this?
Select name, OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') as TriggerIsDisabled from dbo.SysObjects where XType = 'TR' AND 1 = OBJECTPROPERTY(id, 'ExecIsTriggerDisabled')
-
And I'd also go with the one trigger route...
-
I don't get a value from the select statement about the TriggerDisabled. Both triggers fail. When I ran a debug in QA the code never lands on the trigger; it is as if the trigger is not there.
-
Are you sure they are created?
What happens if you run some insert statements/procs from QA?
-
When you debug a stored proc which contains statements that fire a trigger, you do not get to debug the statements in the trigger - even if they are executing - they just execute.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
One trick to debug triggers. You can have some PRINT command in the trigger to print out some debug information. The information will be showed up when you run the SP or insert a new row in the table from QA.
-
Good tip
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
We finally solved our trigger problem. If a row already existed in table STUDENT_T with the SSN value found in INSERTED, we would receive an insert error. Violation of PRIMARY KEY constraint 'PK_STUDENT_T'. Cannot insert duplicate key in object 'STUDENT_T'.
The statement has been terminated.
This would cause the row to not be inserted into the PERSON_T table because the trigger failed.
Here is the fix:
ALTER TRIGGER insertStudent_T
ON SWCSPersonnel.dbo.PERSON_T
FOR INSERT
AS
declare @ssn varchar(9)
select @ssn = ssn from inserted
select ssn from swcsstumgt.dbo.student_t
where ssn = @ssn
--print @ssn
if @@rowcount = 0
begin
INSERT INTO SWCSStuMgt.dbo.STUDENT_T (SSN)
SELECT SSN FROM INSERTED
end
-
Kathy - beware your fix only works if the insert into the initial table is a single row insert - if multi rows are inserted it may faile again - what the body of the trigger should look like is something like:
INSERT INTO SWCSStuMgt.dbo.STUDENT_T (SSN)
SELECT SSN FROM INSERTED i LEFT JOIN SWCSStuMgt.dbo.STUDENT_T st ON i.SSN=st.SSN WHERE st.SSN is null
James Horsley
Workflow Consulting Limited -
Thank you, James. Good suggestion!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply