Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72

    Unanswered: Trigger Not Firing, DTS Load

    I have a table that is getting refreshed from DB2 using DTS (I believe the DBA is doing a DELETE and an APPEND). I have a trigger on this table ON APPEND, INSERT, but the trigger never fires. When I manually update the data, the trigger fires no problem...

    Is DTS capable of updating a SQL Server table without firing the trigger?

    I'm an Oracle guy, and this is my 1st experiences with SQL Server, so I'll put the code here and if you want to point out any bad practices (such as the way i converted the DB2 TIMESTAMP to a SQL Server DATETIME , please do.

    FYI, the DB2 TIMESTAMP is getting loaded into the SQL Server table as a VARCHAR(26)
    Carl

    CREATE TRIGGER trig_SAWakeUp ON tsnro
    FOR INSERT, UPDATE
    AS
    DECLARE @snro_stus char(10)
    DECLARE @snp_sht_dtm as datetime
    SELECT @snro_stus = snro_stus
    FROM tsnro
    IF (RTRIM(@snro_stus) = 'ASSIGNED') OR (RTRIM(@snro_stus) = 'REFRESHED')
    BEGIN
    SELECT @snp_sht_dtm = CONVERT(DATETIME, SUBSTRING(evt_dtm,1,19))
    FROM tsnro

    INSERT INTO TSNP_SHT_DTM (SNRO_STUS, SNP_SHT_DTM )
    VALUES (@snro_stus, @snp_sht_dtm)
    END
    ELSE
    BEGIN
    INSERT INTO TSNP_SHT_DTM ( SNRO_STUS, SNP_SHT_DTM )
    VALUES (@snro_stus, @snp_sht_dtm)
    END

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you want to use the inserted virtual table. I am actually learning Oracle 8 on the job and I think it is equivalent to the new table. To learn please reference SQL Server Books Online\Contents\transact SQL Reference\CREATE TRIGGER.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72
    The problem was we were using Quick Load in DTS, which doesn't hit the logs. We created a seperate package that is last in the batch and disabled Quick Load, and put the trigger on that table. Works fine now, thanks.

    Carl

Posting Permissions

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