Scannning work

oracle Sweden
  • 15 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

  • 15 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.

“To iterate is human, to recurse divine” - L. Peter Deutsch