February 3, 2003 at 7:00 pm
I have the following trigger created
reate trigger ResolutionTrigger
on PMR.dbo.PMR
for Insert, update
as
Begin
Insert PMR_Resolved(PMRID, OpenDate, Fname, Lname, Severity, Problem, Application, Closedate, Resolution)
Select PMRID, OpenDate, Fname, Lname, Severity, Problem, Application, Closedate, Resolution
from PMR
Where closedate ='1'
--Delete record from Open PMR table
Delete from PMR
Where CloseDate = '1'
Here is what is weird, The last 2 statments the delete clause will delete out of the table I sent the record to. any thoughts? I have done this before and had it working but maybe I am overlooking something simple.
Thanks in advance.
Stephen
February 3, 2003 at 9:22 pm
When any records are inserted into PMR the trigger must transfer all with closed=1 to the pmr_resolved table and remove any record in pmr where closed=1? SQL 7 or 2000?
February 4, 2003 at 2:33 am
Can you clarify what you are aiming to achieve from your trigger since it is usual to make use of the Inserted, Deleted tables within the trigger.
February 4, 2003 at 3:42 am
First off in a trigger why are you referencing PMR instead of inserted? When you do an INSERT or UPDATE it will pass thru the inserted internal table that the trigger can see. Then I would UPDATE and set a flag for rows to be deleted. Or instead have logic that hides the CloseDate=1 records from any process referencing the table and have a delete process join the two table on the PK ID value periodically.
WHat I think may be happening is a rollback. Do the values you insert or updated exists in the PMR table still. The reason I think this is happening is the trigger is in the middle of processing and commiting when you do a delete on the records. If the one currently going in or being updated is CloseDate = "1" it will try to delete it. Have you checked for an error message?
Edited by - antares686 on 02/04/2003 03:43:37 AM
February 4, 2003 at 7:17 am
Well what I am trying to do is this.
I have a ASP.NET web application for users to enter problems. I have built the trigger to take care of the issue of the data showing up in the datagrid and it would be easier for reporting that after a record is closed will move it to a different table for said reporting, I am using SQL 7 / 2000 and this trigger worked before on a different database I built with no problems, I am getting no errors it's simply deleting from everything instead of the one table. i have even tried waitfor statement to make it commit the transaction thinking it might be that. If I remove the delete statement at the bottom of the trigger it works like a champ, but then I see the crap I have closed in the Datagrid. It's a simple website and only going to be used to enter problem tickets. Any thougths or examples of what I can do to fix this would be appreciated
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply