Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    42

    Exclamation Unanswered: Trigger to trace updates 2

    Hello guys,

    Thanks for the help on the previous topic, my trigger work fine now... almost...

    For starters, here's my code :
    CREATE TRIGGER trgFGCHTR ON fgchtr
    FOR INSERT, UPDATE, DELETE
    AS
    DECLARE @TypeTrans CHAR(6)
    IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
    SET @TypeTrans = 'UPDATE'
    ELSE
    IF EXISTS(SELECT * FROM inserted)
    SET @TypeTrans = 'INSERT'
    ELSE IF EXISTS(SELECT * FROM deleted)
    SET @TypeTrans = 'DELETE'

    IF @TypeTrans = 'DELETE'
    BEGIN
    INSERT INTO entrepot_audit(trx_type, trx_timestamp, table_name, pkey_virtuo)
    SELECT 'D', getdate(), 'FGCHTR', ak_aux + char(27) + cast(sq as varchar(6))
    FROM deleted
    END

    IF @TypeTrans = 'INSERT'
    BEGIN
    INSERT INTO entrepot_audit(trx_type, trx_timestamp, table_name, pkey_virtuo)
    SELECT 'I', getdate(), 'FGCHTR', ak_aux + char(27) + cast(sq as varchar(6))
    FROM inserted
    END

    IF @TypeTrans = 'UPDATE'
    BEGIN
    INSERT INTO entrepot_audit(trx_type, trx_timestamp, table_name, pkey_virtuo)
    SELECT 'U', getdate(), 'FGCHTR', ak_aux + char(27) + cast(sq as varchar(6))
    FROM deleted
    END

    It works fine if I insert/update/delete records manually in the QA. Our application has been written with Uniface so that we can go with Oracle or SQL Server. The same triggers have been created in Oracle and everyting works fine when the records are updated through the application.

    But with SQL Server, the app freezes. I know my triggers work because the app freezes after the insert/update/delete and my audit table is updated.

    Here is my question. Does SQL Server send back some kind of a message to the application when or after the trigger is executed? It doesn't make sense, but I don't see why it works in Oracle and not in SQL Server. Espacialy a trigger that inserts a line in a table that has nothing to do with the operational database.

    And the problem with Uniface is that it generates the SQL statement and I can't trace it to see what is being sent to the server.

    If you have any ideas, please let me know...

    Thanks a million !

    JFS.
    jean-francois.servant@virtuo-cio.com

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    You can look at the SQL being executed!! Run the SQL Profiler, after connecting to your server you get a "Trace Properties" window, select the "Events" tab, on the left side scroll down to "TSQL", select "SQL:BatchStarting" and click on the "ADD>>" button and then click on the "Run" button. Once this is running, run your Uniface app. You will be able to see all the TSQL statment sent to your server.

    SQL Serve does NOT return any message to an application on successful execution of a trigger UNLESS you have a raiserror, print or uncapatured select statment in your trigger. When you issue an Insert/Update/Delete command in QA you get back exactly what the app gets back.
    Last edited by Paul Young; 09-17-02 at 10:02.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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