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;
Enter your message below
Sign in or Join us (it's free).