Scannning work

oracle Sweden
  • 14 years ago

    Hi
    Im very new in using Oracle. My company has an application that is doing a lot of work during night. I wonder if it is possible do record what kind of work that was done during night, e.g how many rows are inserted or deleted?


    Thanks
    Julia

  • 14 years ago

    You may use trigger as this one:

    CREATE OR REPLACE TRIGGER "OZON"."LOG_TEST" AFTER
    INSERT
    OR UPDATE
    OR DELETE OF "NAME" ON "OZON"."TEST" FOR EACH ROW DECLARE
      v_hour INTEGER;
      v_date VARCHAR2(10);
      v_int INTEGER;
    BEGIN
      v_hour := TO_NUMBER(TO_CHAR(SYSDATE, 'HH24'));
      v_date := TO_CHAR(SYSDATE, 'YYYY/MM/DD');
      IF v_hour > 21 OR (v_hour >0 and v_hour < 9) THEN -- night time
        IF INSERTING THEN
          SELECT count(*) INTO v_int FROM logtable WHERE table_name = 'test' AND logdate=v_date AND operation='INSERT';
          IF v_int > 0 THEN -- update
            UPDATE logtable SET amount = amount + 1 WHERE table_name = 'test' AND logdate=v_date AND operation='INSERT';
          ELSE -- insert
            INSERT INTO logtable (table_name, logdate, operation, amount) VALUES ('test', v_date, 'INSERT', 1);
          END IF;
        ELSIF DELETING THEN
          SELECT count(*) INTO v_int FROM logtable WHERE table_name = 'test' AND logdate=v_date AND operation='DELETE';
          IF v_int > 0 THEN -- update
            UPDATE logtable SET amount = amount + 1 WHERE table_name = 'test' AND logdate=v_date AND operation='DELETE';
          ELSE -- insert
            INSERT INTO logtable (table_name, logdate, operation, amount) VALUES ('test', v_date, 'DELETE', 1);
          END IF;
        ELSIF UPDATING THEN
          SELECT count(*) INTO v_int FROM logtable WHERE table_name = 'test' AND logdate=v_date AND operation='UPDATE';
          IF v_int > 0 THEN -- update
            UPDATE logtable SET amount = amount + 1 WHERE table_name = 'test' AND logdate=v_date AND operation='UPDATE';
          ELSE -- insert
            INSERT INTO logtable (table_name, logdate, operation, amount) VALUES ('test', v_date, 'UPDATE', 1);
          END IF;
        END IF;
      END IF;
    END;

































Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“God could create the world in six days because he didn't have to make it compatible with the previous version.”