Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: Strange trigger problem

    Trigger insert key information from deleted row to the HistoryDB and create DELETED_TblName table to the database if it does not exist and insert row to the DELETED_TblName table.
    Sometimes, i know that user or some job/procedure have deleted eg. 10 rows. After that i have 10 new rows in DELETED_TblName table BUT
    only 2 new rows in H_Update table.
    Why? is there something i miss?
    I have test this hunderd of times and everytyime it works, so now i need little help.. Can You give advice to me : /
    ------------------------------------------------------
    CREATE TRIGGER LCOMCNT_DEL ON [spp].[LCOMCNT]
    FOR DELETE AS

    INSERT INTO HistoryDB..H_UPDATE(
    UPDTABLE,
    UPDFLAG,
    UPDKEY,
    DateDeleted,
    DateStamp)
    SELECT
    'LCOMCNT',
    'DELETE',
    CONVERT(nvarchar(4000),'('+'COMSERNO = ' + '''' + COMSERNO + '''' + ' AND ' + 'CNTSERN = ' + '''' + CNTSERN + '''' + ' AND ' + 'LCCTYPE = ' + '''' + LCCTYPE + '''' + ' AND ' + 'LCCSTART = ' + '''' + LCCSTART + '''' + ')' ),
    GetDate(),
    GetDate()
    FROM Deleted

    IF EXISTS (SELECT * FROM sysobjects WHERE name = 'DELETED_LCOMCNT' AND type='U')
    INSERT INTO spp.DELETED_LCOMCNT
    SELECT * FROM Deleted
    Else
    SELECT * Into spp.DELETED_LCOMCNT FROM Deleted

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There has to be something else going on here, perhaps due to peculiarities in your data. Your code, while unconventional, does not appear to have any syntactical or logical errors.

    You should do some testing to see specifically when this occurs, and with what types of data. For instance, you say only two of ten rows were successfully inserted. Which eight rows were not inserted and what was different about them?

    You should also remove the "SELECT *" syntax and replace it with enumerated column names: "SELECT FIELD1, FIELD2..." and "INSERT INTO spp.DELETED_LCOMCNT (FIELD1, FIELD2,...) SELECT FIELD1, FIELD2..." Its more typing, but it is easier to read and can assist in debugging by generating errors when data does not conform to what you expect. (Errors are good things! Its when something goes wrong and you don't get and error that you really have a long day ahead of you.)

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •