September 11, 2006 at 9:30 am
I am having trouble with triggers.
I am using AD0 in MS VB6 to connect to SQL 2000 and have a fairly large app that needs to incorporate logging of user activity. I determined that building logging into the app would be an extensive programming task and decided to use triggers on key tables to log changes made by the users. The issue I am having is that when the user has chosen to add a new record to the database the trigger fires off and writes the change to my log table with the result being that the unique identifier for the record that the user just created is not returned to the application. It is instead replaced with the id of the last record added to the log table. I have separated the triggers and tried several techniques to write the log before the insert but still have problems and intermittently am returned the wrong ID. The nature of the application requires that I am returned the record that I just added to the user table and so I need a reliable solution for logging the change and being returned the correct information. Any help from someone with experience writing triggers will be greatly appreciated.
September 11, 2006 at 9:56 am
You say that the wrong ID value is being returned to your app. Triggers do not return values so how are you returning the ID to your app? It may help if you post your trigger code and stored procedure code if the INSERT is in a stored proc.
September 11, 2006 at 10:22 am
The statements below are the latest example of an insert trigger that I am trying to use. I am not using a stored procedure to insert the record. The record is being inserted through ADO directly into the database and without a trigger on the VB side of things the current record in my record set will be the last added record. This trigger seems to queer the process and if I try to pull the MTGID which is the key field from the MPromo table then what I will be returned is the AuditID from the AuditDetail Table.
--***************************** Trigger Sample ***********************************************************
CREATE TRIGGER [I_MPromo] ON [dbo].[MPromo]
INSTEAD OF INSERT
AS
DECLARE @NewString NVARCHAR(4000)
SELECT @NewString =
'MTGID: ' + CONVERT(NVARCHAR, MTGID) + CHAR(13) +
'PropID: ' + CONVERT(NVARCHAR, PropID) + CHAR(13) +
'MDesc: ' + CONVERT(NVARCHAR, MDesc) + CHAR(13) +
'MType: ' + CONVERT(NVARCHAR, MType) + CHAR(13) +
'DayOrYear: ' + CONVERT(NVARCHAR, DayOrYear) + CHAR(13) +
'MLow: ' + CONVERT(NVARCHAR, MLow) + CHAR(13) +
'MHigh: ' + CONVERT(NVARCHAR, MHigh) + CHAR(13) +
'Active: ' + CONVERT(NVARCHAR, Active) + CHAR(13) +
'EvalField: ' + CONVERT(NVARCHAR, EvalField) + CHAR(13) +
'PAction: ' + CONVERT(NVARCHAR, PAction) + CHAR(13) +
'Gender: ' + CONVERT(NVARCHAR, Gender) + CHAR(13) +
'Deleted: ' + CONVERT(NVARCHAR, Deleted) + CHAR(13) +
'Archived: ' + CONVERT(NVARCHAR, Archived) + CHAR(13) +
'IsPocketPromo: ' + CONVERT(NVARCHAR, IsPocketPromo) + CHAR(13) +
'AllowMultiSub: ' + CONVERT(NVARCHAR, AllowMultiSub) + CHAR(13)
FROM INSERTED
INSERT INTO Audit (SourceTable, MGTUser, WinUser)
VALUES ('MPromo', 0, SYSTEM_USER)
DECLARE @AuditID NUMERIC
SET @AuditID = SCOPE_IDENTITY()
INSERT INTO AuditDetail (AuditID, AuditType, Data1)
VALUES (@AuditID, 1, @NewString)
INSERT INTO MPromo( PropID, MDesc, MType, DayOrYear, MLow, MHigh, Active, EvalField, PAction, Gender, Deleted, Archived, IsPocketPromo, AllowMultiSub)
SELECT PropID, MDesc, MType, DayOrYear, MLow, MHigh, Active, EvalField,
PAction, Gender, Deleted, Archived, IsPocketPromo, AllowMultiSub FROM Inserted
-- ************************************ End Trigger Sample*********************************
‘*********************** VB Code Sample Follows **********************************
Dim mRS AS ADODB.Recordset
Dim mSTRSQL AS String
Dim mCNN As String
Dim mMTGID AS Double
mCNN = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & Trim(mDatabase) & ";Data Source=" & Trim(mServer)
mSTRSQL = "SELECT * FROM MPromo WHERE (MTGID = 0)"
Set mRS = New ADODB.Recordset
With mRS
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open mSTRSQL, mCNN, , , adCmdText
.AddNew
!PropID = mPropID
!mDesc = mDesc
!mType = mType
!DayOrYear = mDateType
!MLow = mMPLow
!MHigh = mMPHigh
!Active = mActive
!Gender = mGender
!EvalField = mEvalField
!PAction = mPAction
!IsPocketPromo = mIsPocketPromo
!AllowMultiSub = mAllowMultiSub
.Update
mMTGID = !MTGID
End With
September 11, 2006 at 11:03 am
scope identity is going to return the identity value on I_MPromo table. Looks like you want the ID from the Audit Header table.If so you can use identity. Hope this answers your question.
Thanks
Sreejith
September 12, 2006 at 2:48 pm
The problem is you are using an INSTEAD OF INSERT trigger when an AFTER INSERT trigger would work better. With the INSTEAD OF trigger you have to do all the database updating from within the trigger while the AFTER trigger allows the initial action (INSERT) to occur on the table and then allows you to do something additional (insert into the audit table, etc.).
I'm not fully sure on this, but I would think that the AuditID field would be available from the inserted table.
HTH
Steve B.
September 13, 2006 at 3:24 am
@sblock: I agree with you that you wouldn't want to use an INSTEAD OF trigger in this case. You should only use it, if you want to manipulate the data that is used for the INSERT.
So use an AFTER trigger. And then find out the differences between IDENTITY() and SCOPE_IDENTITY() like Sreejith Sreedharan mentioned:
IDENTITY() returns the last created ID somewhere in your database. So, if you do an INSERT, IDENTITY() probably returns the ID you just inserted. BUT, you don't know for sure. If you don't use transactions, another user could have inserted some other data just before your SELECT IDENTITY() was executed. Then you retrieve a complete different value. Also like your case, if a trigger is fired after your insert, you could retrieve another value that your trigger just caused. Therefore you should use SCOPE_IDENTITY(). SQL Server then gets the last inserted ID within your scope!
See http://msdn2.microsoft.com/en-us/library/ms190315.aspx for more info.
September 13, 2006 at 10:23 am
Thank you all for trying to help. The reason I am using an insert trigger is because the After Insert trigger produced the condition in the first place so I decided to use an instead of trigger so that the last record saved in a single transaction would be the actual write to the user table.
If you examine the code you will realize that I am specifically looking for the Identifier value from the user table by the name of the field. SQL Server is returning me the identifer for the log table instead. The Identity field in the Log table has a completely different name yet is returned to my recordset as the Idenity field for the user table.
This behavior is occouring on the Server side and when the trigger is removed the VB code works every time.
ADO Seems to use the same process to retrieve the last record saved as the trigger itslef. The result is that my recordset, while it contains the other data I have saved does not contain the correct identity value for that record.
September 13, 2006 at 12:14 pm
Maybe you want to use IDENT_CURRENT to get the last identity value generated for a specific table. From BOL
Steve B.
September 14, 2006 at 3:37 am
Mark, in your case you shouldn't use IDENT_CURRENT if you don't use transactions. Requesting the IDENT_CURRENT could provide a newer ID that another user just inserted before you were able to request the IDENT_CURRENT. I still recommend the SCOPE_IDENTITY for this covers your session and scope instead of any session and scope.
A trigger fired after an insert will be part of your session, but won't be part of your scope! Therefore the SCOPE_IDENTITY will give you back the correct id. I'm not exact sure what happens if you keep on using a INSTEAD OF trigger, because the actual insert then happens outside your scope... Thus, use an AFTER trigger.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply