[dba-SQLServer] Controlling Trigger

Furrr-ghed-about-it!!

hehehe. I am a prize goose.

I forgot to add in "Inserted" at the bottom... duh!

Should read  "FROM Inserted ITFCT_tblBaselineData"
rather than  "FROM ITFCT_tblBaselineData"

all good now. :)

cheers
Darryl.


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Darryl
Collins
Sent: Thursday, 18 September 2008 4:54 PM
To: 'Discussion concerning MS SQL Server'
Subject: [dba-SQLServer] Controlling Trigger



Hi Everyone,

I am trying to create an transaction audit table using a trigger - I have one for Udate, Delete, Insert. However, whenever I make a change the trigger will record every record in the table as being update, rather than just the txn in question.  I can see why this happens (as it is pretty much what i have coded), But how do I pass the active record KeyID (BaselineID in this case) to the SQL Trigger from Access?  All I want to record is the record in the table that has changed, but am getting the entire table instead.

Any Pointers?

Cheers
Darryl.

Code below:


' --------------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


/* #############################################################*/
/*      Changes to ITFCT_tblBaselineData UPDATE Trigger */
/* #############################################################*/

        ALTER   TRIGGER trgUpd_tblBaseline
        ON ITFCT_tblBaselineData
        FOR UPDATE AS

        INSERT INTO [dbo].ITFCT_audBaselineData
                ([BaselineID],
                [IsCurrent],
                [ProductCode],
                [ProjectCode],
                [SkillGroup],
                [Component],
                [ResourceOrItem],
                [StandardTaskID],
                [StandardTaskAdd],
                [MECID],
                [SpendTypeID],
                [AccTreatmentID],
                [AccountingSplit],
                [ValueAUD],
                [ValueOpex],
                [ValueCapex],
                [SourceID],
                [ForumRef],
                [ReleaseNote],
                [FYID],
                [Comments],
                [APLID],
                [EventTrigger]
                )

        SELECT [BaselineID],
                [IsCurrent],
                [ProductCode],
                [ProjectCode],
                [SkillGroup],
                [Component],
                [ResourceOrItem],
                [StandardTaskID],
                [StandardTaskAdd],
                [MECID],
                [SpendTypeID],
                [AccTreatmentID],
                [AccountingSplit],
                [ValueAUD],
                [ValueOpex],
                [ValueCapex],
                [SourceID],
                [ForumRef],
                [ReleaseNote],
                [FYID],
                [Comments],
                [APLID],
                'UPDATE'
        FROM ITFCT_tblBaselineData
        WHERE [BaselineID] = [BaselineID] ''' THIS BIT HERE NEEDS A TWEAK



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

' --------------------------------------------------------------

This email and any attachments may contain privileged and confidential information
and are intended for the named addressee only. If you have received this e-mail in
error, please notify the sender and delete this e-mail immediately. Any
confidentiality, privilege or copyright is not waived or lost because this e-mail
has been sent to you in error. It is your responsibility to check this e-mail and
any attachments for viruses.  No warranty is made that this material is free from
computer virus or any other defect or error.  Any loss/damage incurred by using this
material is not the sender's responsibility.  The sender's entire liability will be
limited to resupplying the material.


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


This email and any attachments may contain privileged and confidential information
and are intended for the named addressee only. If you have received this e-mail in
error, please notify the sender and delete this e-mail immediately. Any
confidentiality, privilege or copyright is not waived or lost because this e-mail
has been sent to you in error. It is your responsibility to check this e-mail and
any attachments for viruses.  No warranty is made that this material is free from
computer virus or any other defect or error.  Any loss/damage incurred by using this
material is not the sender's responsibility.  The sender's entire liability will be
limited to resupplying the material.