Privet vsem! Znachit u mine yest takoy trigger kotoriy napisivayet na log tablitsu vse izmeneniye nad bazoy. Trigger vot takoy!
CREATE OR REPLACE TRIGGER faiq_trigger
AFTER CREATE OR ALTER OR DROP ON DATABASE
DECLARE
l_sysevent VARCHAR2 (250);
l_extra VARCHAR2 (4000);
BEGIN
SELECT ora_sysevent
INTO l_sysevent
FROM DUAL;
IF (l_sysevent IN ('DROP', 'CREATE'))
THEN
IF l_sysevent = 'CREATE'
THEN
BEGIN
SELECT 'storage (initial '
|| initial_extent
|| ' next '
|| next_extent
|| ' ....)'
INTO l_extra
FROM all_tables
WHERE table_name = ora_dict_obj_name AND owner = USER;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
END IF;
INSERT INTO LOG
SELECT USER, SYSDATE, SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, l_extra,
ora_dict_obj_type
FROM DUAL;
ELSIF (l_sysevent = 'ALTER')
THEN
INSERT INTO LOG
SELECT USER, SYSDATE, SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name,
sql_text, ora_dict_obj_name
FROM v$open_cursor
WHERE UPPER (sql_text) LIKE 'ALTER%' || ora_dict_obj_name || '%'
AND SID = (SELECT SID
FROM v$session
WHERE audsid = USERENV ('sessionid'));
END IF;
END;
/
Ona pravilna rabotayet. No shas nashi proqramisti napisali odnu proceduru kotoriy aktualen dlya nahsey raboti. Procedure normalno rabotayet na testavoy baze. No i toqda mi podkluchili etu proceduru na promishlenniy bazu. Procedura normalno kompiliruyetsa s useram system no ne idet kompilasiya pod useram SYS. Ona dayot oshibku kak "vozvrashayetsa mnoqo chem odna zapis"(chto ta paxoje mejdu "IN" i " = "). I poetamu mi ne mojem podkluchit yeyo na Job. Chto mine delat? Pomaqiti yesli smojeti. Zaranye sposibo!