December 14, 2006 at 1:50 pm
Hello, I need to develop a trigger for a third party application for auditing purposes. The trigger needs to tell me what all the values were in the row that is being deleted when the stored procedure is run.
Here are the fields I need to capture
feeStudentId
,feeid
,StudentId
,FeeDate
,FeeAmount
,TotalPaid
,SessionId
,dAteModified
,UserId
Here is the store procedue I need to modify
PrOCEDURE dbo.proc_FeeStudent_Remove
@FeeStudentId udtId
, @rv timestamp = NULL OUTPUT
, @SessionId udtID = NULL
, @userid udtId
AS
/**/
Declare @PreviousRowVersion timestamp
Declare @EC int
Declare @OpenTrans int
SET NOCOUNT ON
set @OpenTrans = @@TRANCOUNT
Begin Tran
select @PreviousRowVersion=
(
select rv
from tblFeeStudent UPDLOCK
where FeeStudentId=@FeeStudentId
)
SET @EC = @@Error
IF @EC <> 0 GOTO ERROR
exec proc_Checkrv @rv, @PreviousRowVersion
SET @EC = @@Error
IF @EC <> 0 GOTO ERROR
DELETE
FROM tblFeeStudent
where FeeStudentId=@FeeStudentId
SET @EC = @@Error
IF @EC <> 0 GOTO ERROR
COMMIT TRAN
RETURN 0
ERROR:
WHILE @OpenTrans < @@TRANCOUNT
ROLLBACK TRAN
RETURN @EC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks for any Help
December 14, 2006 at 6:08 pm
This is just a quick post, but...
1. create a table to hold the deleted values, something like ...
CREATE TABLE dbo.deletedValues
(deletedKey identity int (1, 1)
,feeStudentId int
,feeid int
,StudentId int
,FeeDate datetime
,FeeAmount money
,TotalPaid money
,SessionId int
,dAteModified datetime
,UserId int)
2. in your BEFORE trigger, something like ...
insert dbo.deletedValues
select
feeStudentId
,feeid
,StudentId
,FeeDate
,FeeAmount
,TotalPaid
,SessionId
,dAteModified
,UserId
from
deleted
and that should pretty much do it.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 15, 2006 at 7:17 am
I was able to create the trigger no problem but when I go to test it causes the application to crash. here is the information getting passed into the delete stored procedure
proc_FeeStudent_Remove
@FeeStudentId = '5ec707da-5689-431e-811a-989200180aa6'
, @rv = NULL
, @SessionId = 'eecbb7cc-ce12-41d8-9d34-989800926ffc'
, @userid = '0bb98533-87d5-4681-8e3f-2ff55df7122c'
here is the table and trigger inserted
CREATE TABLE dbo.deletedFeeValues
(deletedKey udtId
,FeeStudentId udtId
,feeid udtId
,StudentId udtId
,FeeDate smalldatetime
,FeeAmount money
,feeStatusID tinyint
,TotalPaid money
,SessionId udtId
,dAteModified udtId
,UserId udtId
,rv timeStamp
)
create trigger feeStudentDelete
on tblfeeStudent
for DELETE
as
Insert deletedFeeValues
select
FeeStudentId
,feeid
,StudentId
,FeeDate
,FeeAmount
,feeStatusID
,TotalPaid
,SessionId
,dAteModified
,UserId
,rv
from
feeStudentDelete
December 15, 2006 at 7:24 am
the FROM clause must reference the "DELETED" table. This holds the info that is being changed/deleted.
Also, I'm not familiar with "udtId"
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 15, 2006 at 7:58 am
Ok I modified it now I get the following error
cannot insert a non-null value into a timestamp column
CREATE TABLE [tblFeeDelete] (
deletedKey int identity (1, 1),
[FeeStudentId] [udtId] NULL ,
[FeeId] [udtId] NULL ,
[StudentId] [udtId] NULL ,
[FeeDate] [smalldatetime] NULL ,
[FeeAmount] [money] NULL ,
[TotalPaid] [money] NULL,
[FeeStatusId] [tinyint] NULL ,
[DateModified] [udtTrackingDate] NULL ,
[rv] [timestamp] NULL ,
[UserId] [udtId] NULL ,
[SessionId] [udtId] NULL ,
)
set Identity_INSERT tblFeeDelete ON
create trigger feeStudentDelete
on tblfeeStudent
for DELETE
as
Insert tblFeeDelete
select
[FeeStudentId] ,
[FeeId] ,
[StudentId] ,
[FeeDate] ,
[FeeAmount] ,
[TotalPaid] ,
[FeeStatusId] ,
[DateModified] ,
[rv] NULL ,
[UserId] ,
[SessionId]
from tblFeeStudent
December 15, 2006 at 8:20 am
1. You don't need..."set Identity_INSERT tblFeeDelete ON"
2. Change your insert to the following.
Insert tblFeeDelete
([FeeStudentId] ,
[FeeId] ,
[StudentId] ,
[FeeDate] ,
[FeeAmount] ,
[TotalPaid] ,
[FeeStatusId] ,
[DateModified] ,
[UserId] ,
[SessionId] )
select
[FeeStudentId] ,
[FeeId] ,
[StudentId] ,
[FeeDate] ,
[FeeAmount] ,
[TotalPaid] ,
[FeeStatusId] ,
[DateModified] ,
[UserId] ,
[SessionId]
from DELETED
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 15, 2006 at 8:26 am
It is now working but the issue now is that when the trigger is called it just dumps all the records in from tblFeeStudent into the tblFeeStudentDelete
December 15, 2006 at 8:33 am
This is why you use the DELETED in your from clause.
SELECT
[FeeStudentId] ,[FeeId] ,[StudentId] ,
[FeeDate] ,[FeeAmount] ,[TotalPaid] ,
[FeeStatusId] ,[DateModified] ,[UserId] ,
[SessionId]
FROM
DELETED
this will only give you what is being changed/removed
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 15, 2006 at 8:58 am
As you can see from my post I am definate newbie but Im not sure I follow your reference to DELETED. I never explicitly created that table can I use the original stored procedure in the from statement?
December 15, 2006 at 9:02 am
Books online would be your best friend at the moment. The Inserted and Deleted tables are accessible only within triggers. They contain the information on the data that has been altered by the statement that made the trigger fire in the first place.
Inserted will keep the data of newly inserted rows
Deleted will keep the data of deleted rows
In the case of an update, the inserted table will hold the new version of the updated rows while the deleted table will hold the old version of those same rows.
December 15, 2006 at 10:50 am
Sorry for the miscommunication the trigger is working great. The trigger will capture userid and session id when you created the trigger but will make it null if someone else inserted the fee. Is it possible to insert the userid and session id from a different table.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply