Post reply

Insert trigger causing errors in VB6 app.

  • March 16, 2005 at 3:42 pm

    #67170

    I have had no previous experience with triggers, and am having trouble getting this to function.

    On Orders table, Id is primary key, I was asked to create an means of tracking all changes to 1 specific field, PkgCodeID.

    I created a table:

    CREATE TABLE [OrdersAudit] (

     [EditID] [int] IDENTITY (1, 1) NOT NULL ,

     [OrderID] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

     [PkgCodeIDOrig] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [PkgCodeIDNew] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [EditCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [EditDate] [datetime] NOT NULL ,

     [ModifiedBy] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     CONSTRAINT [PK_OrdersAudit] PRIMARY KEY  CLUSTERED

     (

      [EditID]

    &nbsp  ON [PRIMARY]

    ) ON [PRIMARY]

    Right now, I am just trying to get the insert trigger to work. If I can get it working, I will add the update and delete to the trigger.

    The following worked fine before I added the commented Where clause at the end:

    CREATE TRIGGER dbo.Orders_tri ON Orders FOR INSERT

    AS

    INSERT dbo.OrdersAudit

     SELECT

        ID

        , ''

        , PkgCodeID

        , 'I'

        , getdate()

        , suser_sname()

     FROM inserted i

     --WHERE i.PkgCodeID IS NOT NULL

     RETURN

    Since the app that adds most of the records seems to add the record first then updates the audited field later, Nearly all of the inserts were null. When I added the Where clause, the VB6 app began returning the following error: Row cannot be located for updating. Some values may have been changed since it was last read. When I tried it on a test table, I could go in and manually do inserts and updates and it worked as expected.

    Any thoughts as to why this might be happening?

  • March 16, 2005 at 4:02 pm

    #547411

    How are you calling this from VB ? Are you trying to open a recordset on the result, are you exec'ing an 'INSERT ....' SQL statement, or calling a stored procedure ?

     

  • March 16, 2005 at 4:12 pm

    #547413

    Since I didn't write the app, I am not sure except that it is not a stored procedure. I am familiar with all of the stored procedures on the server, and it is not being used.  I will try to get more details from the developer.

    Thanks for the quick response.

  • March 16, 2005 at 4:44 pm

    #547417

    You could attempt to find out what the app is sending by finding the process id it has connected with and executing:

    dbcc inputbuffer(spid)

    ... to get the last SQL statement sent on the connection.

    Or turn on profiling to capture all the SQL sent from the app to the server.

  • March 17, 2005 at 11:15 am

    #547621

    Thanks Pw,

    Using profiler, I think I know why it is not working. The executing command: exec sp_executesql N'INSERT INTO "<DataBaseName".."Orders" ("Id",...) VALUES (@P1,...) is not including the PkgCodeID field in the field list, so I assume that that field does not exist in the inserted table.

    How is the best way to accomplish this then?

    I want to log inserts only if PkgCodeID is not NULL. If I get that to work, I am also going to log all updates (to the PkgCodeID field only) and all deletes. BOL shows COLUMNS_UPDATED() with the bitmask of the PkgCodeID field, and I think I understand it, but is that the only way of doing this, or is there a way of addressing the field by name to do this?

    Thanks for your help.

  • March 17, 2005 at 11:53 am

    #547633

    Your initial code would do it just uncomment the where clause.

    For Update triggers use the construct:

    IF UPDATE(PkgCodeID)

     begin

      --- Your Code Here

     end

     and for the delete trigger just use the "deleted" virtual table

    hth


    * Noel

  • March 17, 2005 at 12:02 pm

    #547637

    noeld,

    When the where clause was uncommented was when the VB app began getting the: "Row cannot be located for updating. Some values may have been changed since it was last read." error on attempted inserts. As long as it is commented, it works great, except that 95% of the PkgCodeID values logged are NULL.

  • March 17, 2005 at 12:17 pm

    #547643

    Donald,

    1. The "inserted" virtual table is an image (in structure) of the Table where the trigger was defined. Therefore it does not matter what field the application is sending to the server at trigger time.

    2. Do you have other TRIGGERS defined on that table? or on the Audit Table?

    3. One last thing on the insert statement in your trigger you should specify a field list ex:

     INSERT dbo.OrdersAudit (ID,Fld2,PKG,ACTION,Dte,ModifiedBy)

     SELECT

        ID

        , ''

        , PkgCodeID

        , 'I'

        , getdate()

        , suser_sname()

     FROM inserted i

     --WHERE i.PkgCodeID IS NOT NULL

     RETURN

    oh another also very usefull thing is to include this  check at the begining of the trigger

    if @@rowcount = 0 RETURN

    hth

     


    * Noel

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply