TRIGGER / monitoring zmiany danych

0

Witam serdecznie,

mam jeden mały problem. Mam TRIGGERa, który monitoruje następujące sytuacje:

1)gdy zmienione zostaną dane w tab. employess to zapisze zmiany w tab emp_monit -- nie umiem zapisać który to rekord został zmieniony :(
2)gdy zostanie dodany wiersz do tab. employess i zapisuje zmiany w tab emp_monit i który rekord został zmieniony -- to mi działa :)
3)gdy zostanie usunięty wiersz z tab. employess i zapisuje zmiany w tab emp_monit -- z tym juz sonie poradze jak zrobie upload

Umiem zrobić kto zmieniał dane, w jakim czasie i jaką operacje wykonał, ale brakuje mi zapisu który rekord w tabeli zmienił się. Proszę o jakąś wskazówkę ...

tabela employess przedstawia się:

CREATE TABLE employess (
id NUMBER(4) NOT NULL PRIMARY KEY,
first_name VARCHAR2(30),
last_name VARCHAR2(30),
email VARCHAR2(40),
phone NUMBER(12),
jobs_id NUMBER(4),
salary NUMBER(4),
departments_name VARCHAR2(30)
);
ALTER TABLE employess
ADD FOREIGN KEY (departments_name)
REFERENCES departments(name);

ALTER TABLE employess
ADD FOREIGN KEY (jobs_id)
REFERENCES jobs(id);

Zmiany 1),2) i 3) są zapisywane do tabeli

CREATE OR REPLACE TYPE tab_VARRAY IS VARRAY(3) OF VARCHAR2(50);
/
CREATE TABLE emp_monit (
id NUMBER(4) primary key,
user_name VARCHAR2(30),
oper_date TIMESTAMP, -- czas operacji
oper_type VARCHAR2(1),
oper_comment tab_VARRAY -- jakie rekordy zmienił
);
TRIGGER tak wygląda:

CREATE TYPE tab_VARRAY AS VARRAY(3) OF VARCHAR2(50);
/
show err

CREATE OR REPLACE TRIGGER emp_x
AFTER INSERT OR DELETE OR UPDATE
ON employess
FOR EACH ROW
DECLARE
v_oper VARCHAR2(1);
TYPE tab_VARRAY IS VARRAY(3) OF VARCHAR2(50);
tab_VAR tab_VARRAY := tab_VARRAY(NULL,NULL,NULL);
i number(5);
BEGIN
IF INSERTING THEN v_oper := 'I';
ELSIF UPDATING THEN v_oper := 'U';
ELSIF DELETING THEN v_oper := 'D';
END IF;
if :new.id IS NOT NULL THEN tab_VAR(1):=('id'); end if;-- tu mi zapisuje jak insert into .. i że dodałam ten rekord do tab.emp_monit
if :new.first_name IS NOT NULL THEN tab_VAR(2):=('first_name'); end if;-- działa jak wyżej :)
if :new.last_name IS NOT NULL THEN tab_VAR(3):=('last_name'); end if; -- działa jak wyżej :)

INSERT INTO emp_monit VALUES (:new.id, user, systimestamp, v_oper, tab_VARRAY(tab_VAR(1),tab_VAR(2),tab_VAR(3)));
END;
/
show err

Uprawnienia mam nadane, bo mi usuwa, dodaje i zmienia dane na innych tabelach tego samego użytkownika. Tylko stoję z tym upload . Próbowałam tak:

if :new.id IS NOT NULL or :new.id != :old.id THEN tab_VAR(1):=('id'); end if
ELSIF UPDATING and :new.id != :old.id THEN v_oper := 'U' and tab_VAR(1):=('id');

ale nie działa. Proszę uprzejmie o jakąś wskazówkę ... jak zapisać, który jest rekord z tab. employess został zmieniany do tabeli emp_monit

0

jak cos jest wstawiane jest w inserting (tylko!)
jak cos jest usuwane jest w deleting (tylko!)
jak cos jest updatowane jest w tabeli inserting i deleting

wiec (zakladam ze kluczem glownym jest pole id):
select * from Inserting I
inner join Deleting D on I.id = D.id

pomijam juz wybranie odpowiednich pol i sprawdzenie zmian w polach, mysle ze dasz rade, jak nie to pisz

0

ale ja nie mam tabeli INSERTING, UPDATING i DELETING. Mam tylko tabele employess i emp_monit

CREATE TABLE emp_monit (
id NUMBER(4) primary key, -- id tabeli
user_name VARCHAR2(30), -- kto zmienił dane
oper_date TIMESTAMP, -- czas zmiany lub dodania lub usunięcia
oper_type VARCHAR2(1), -- typ operacji I lub U lub D
oper_comment tab_VARRAY /* tablica zagnierzdzona w której odpowiednio sie zapisuje rekord czy został dodany,usuniety lub zmieniony */
);

nie mogę nawet usunąć wiersza i zmienić jak mój wyzwalacz, chce zapisać rekordy do tab zagnieżdżonej.

oper_comment tab_VARRAY

gdy np daje polecenie: delete from employess where id =2;

pojawia mi sie bład:
ORA-01400: cannot insert NULL into ("KASIA"."EMP_MONIT"."ID")
ORA-06512: at "KASIA.EMP_X", line 25
ORA-04088: error during execution of trigger 'KASIA.EMP_X'

Jak nie mam zapisywania który rekord jest modyfikowany, to wszystko działa, ale niestety i to muszę zrobić. Można prosić o wskazówkę do tego błędu...

0

ale ja nie mam tabeli INSERTING, UPDATING i DELETING. Mam tylko tabele employess i emp_monit

Masz. Wspomniane tabele (z tego co pamiętam to INSETRED i DELETED - INSTERTING, UPDATING i DELETING to flagi) są osiągalne w trakcie wykonywania triggera (oczywiście, jak wcześniej wpomniał massther odpowiednie tabele w odpowiedniej sytuacji).

0

domyślałam się że mogą istnieć takie tab. :) , ale bardziej przekonywała mnie myśl ze to polecenie

teraz mi działa ale tylko 1 , jak już zmieniam ten sam rekord drugi raz pojawia się błąd że jest duplikat klucza. Nie wiem za bardzo jak to naprawić? Massther podał mi informację:

select * from Inserting I
inner join Deleting D on I.id = D.id

ale nie wiem gdzie ją umieścić ..?

Tak napisałam TRIGGER:

CREATE OR REPLACE TRIGGER emp_x
AFTER INSERT OR DELETE OR UPDATE
ON employess
FOR EACH ROW
DECLARE
v_oper VARCHAR2(1);
TYPE t_VARRAY IS VARRAY(8) OF VARCHAR2(50);
tab_VAR t_VARRAY := t_VARRAY(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

id employess.id%TYPE;
first_name employess.first_name%TYPE;
last_name employess.last_name%TYPE;
email employess.email%TYPE;
phone employess.phone%TYPE;
jobs_id employess.jobs_id%TYPE;
salary employess.salary%TYPE;
departments_name employess.departments_name%TYPE;

BEGIN
IF INSERTING THEN v_oper := 'I';
ELSIF UPDATING THEN v_oper := 'U';
ELSIF DELETING THEN v_oper := 'D';
END IF;
--select * from Inserting I inner join Deleting D on I.id = D.id;

if UPDATING and :old.id != :new.id THEN tab_VAR(1):=('id'); end if;
if UPDATING and :old.first_name != :new.first_name THEN tab_VAR(2):=('first_name'); end if;
if UPDATING and :old.last_name != :new.last_name THEN tab_VAR(3):=('last_name'); end if;
if UPDATING and :old.email != :new.email THEN tab_VAR(4):=('email'); end if;
if UPDATING and :old.phone != :new.phone THEN tab_VAR(5):=('phone'); end if;
if UPDATING and :old.jobs_id != :new.jobs_id THEN tab_VAR(6):=('jobs_id'); end if;
if UPDATING and :old.salary != :new.salary THEN tab_VAR(7):=('salary'); end if;
if UPDATING and :old.departments_name != :new.departments_name THEN tab_VAR(8):=('departments_name'); end if;

INSERT INTO emp_monit VALUES (:new.id, user, systimestamp, v_oper, t_VARRAY(tab_VAR(1),tab_VAR(2),tab_VAR(3),tab_VAR(4),tab_VAR(5),tab_VAR(6),tab_VAR(7),tab_VAR(8)));
END;
/
show err

jak wykonuje polecenie:
UPDATE employess SET first_name='zzz' WHERE id=7;

select column_value from the(select CAST(oper_comment as t_VARRAY) from emp_monit where id=7);

to działa, ale tylko raz :(

0

jak zrobisz
update employess
set salary = 1000
where id between 5 and 10 -- zalozmy ze jest ciaglosc id i ze salary bylo inne niz 1000

to w trigger dostaniesz 6 rekordow w Inserted i 6 w Updated
trigger nie odpala sie dla kazdego wiersza osobno, moze odpalic sie na grupy wierszy (czemu? wydajnosc)
wiecej trigger moze odpalic sie raz dla nastepujacych po sobie operacji np. insert, update, delete
wtedy bedziesz miala rozna liczbe wierszy w inserted, updated, deleted
poza tym new.id dla update zdaje sie ze jest bez zmian, wiec jesli najpierw zalogowalas wstawienie, a pozniej update danego wiersza to dostaniesz blad, bo id w emp_monit jest kluczem, a juz przy insert dodalas rekord z takim id
wiec w emp_monit powinnas miec klucz glowny jako auto_increment, taka dodatkowa kolumna, bo w koncu, dany rekord moze byc wiele razy aktualizowany

0

DZIĘKUJE SERDECZNIE JUŻ DZIAŁA MI :) :) :)

poniżej zamieszczam kod, może komuś się przyda

CREATE OR REPLACE TYPE t_VARRAY IS VARRAY(8) OF VARCHAR2(50);
/
-- usuniecie typu --
drop type t_VARRAY;
-- tabela monitorująca
CREATE TABLE emp_monit (
id NUMBER(4) PRIMARY KEY ,
user_name VARCHAR2(30),
oper_date TIMESTAMP,
oper_type VARCHAR2(1),
oper_comment t_VARRAY
);

--------- auto_increment ---
CREATE SEQUENCE emp_monit_sequence
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER emp_monit_trigger
BEFORE INSERT
ON emp_monit
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT emp_monit_sequence.nextval INTO :NEW.id FROM dual;
END;
/
show err

CREATE OR REPLACE TRIGGER emp_x
BEFORE INSERT OR DELETE OR UPDATE
ON employess
FOR EACH ROW
DECLARE
v_oper VARCHAR2(1);
TYPE t_VARRAY IS VARRAY(8) OF VARCHAR2(50);
tab_VAR t_VARRAY := t_VARRAY(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
id employess.id%TYPE;
first_name employess.first_name%TYPE;
last_name employess.last_name%TYPE;
email employess.email%TYPE;
phone employess.phone%TYPE;
jobs_id employess.jobs_id%TYPE;
salary employess.salary%TYPE;
departments_name employess.departments_name%TYPE;
BEGIN
IF INSERTING THEN v_oper := 'I';
ELSIF UPDATING THEN v_oper := 'U';
ELSIF DELETING THEN v_oper := 'D';
END IF;
--select * from Inserting I inner join Deleting D on I.id = D.id;

<span style="color: blue">if </span>INSERTING <span style="color: blue">and :new</span>.first_name<span style="color: blue"> IS NOT NULL  THEN </span>tab_VAR(2):=('first_name'); <span style="color: blue">end if;</span>
if INSERTING <span style="color: blue">and :new</span>.last_name  <span style="color: blue">IS NOT NULL  THEN </span>tab_VAR(3):=('last_name'); <span style="color: blue"> end if;</span>
<span style="color: blue">if </span>INSERTING <span style="color: blue">and :new</span>.email 	<span style="color: blue"> IS NOT NULL  THEN</span> tab_VAR(4):=('email');       <span style="color: blue">end if;</span>
<span style="color: blue">if </span>INSERTING <span style="color: blue">and :new</span>.phone      <span style="color: blue">IS NOT NULL  THEN</span> tab_VAR(5):=('phone');    <span style="color: blue">  end if;</span>
<span style="color: blue">if </span>INSERTING <span style="color: blue">and :new</span>.jobs_id   <span style="color: blue"> IS NOT NULL  THEN</span> tab_VAR(6):=('jobs_id');   <span style="color: blue"> end if;</span>
<span style="color: blue">if </span>INSERTING <span style="color: blue">and :new</span>.salary    <span style="color: blue"> IS NOT NULL  THEN</span> tab_VAR(7):=('salary');     <span style="color: blue">end if;</span>
<span style="color: blue">if </span>INSERTING <span style="color: blue">and :new</span>.departments_name <span style="color: blue">IS NOT NULL THEN</span> tab_VAR(8):=('departments_name'); <span style="color: blue">end if;</span>

<span style="color: blue">if </span>UPDATING <span style="color: blue">and :old</span>.first_name  != <span style="color: blue">:new.first</span>_name  <span style="color: blue">THEN </span>tab_VAR(2):=('first_name'); <span style="color: blue">end if;</span>
<span style="color: blue">if </span>UPDATING <span style="color: blue">and :old</span>.last_name   != <span style="color: blue">:new.last</span>_name   <span style="color: blue">THEN </span>tab_VAR(3):=('last_name');  <span style="color: blue">end if;</span>
<span style="color: blue">if </span>UPDATING <span style="color: blue">and :old</span>.email       != <span style="color: blue">:new</span>.email 	     <span style="color: blue">THEN </span>tab_VAR(4):=('email');       <span style="color: blue">end if;</span>
<span style="color: blue">if </span>UPDATING <span style="color: blue">and :old</span>.phone       != :<span style="color: blue">new</span>.phone       <span style="color: blue">THEN </span>tab_VAR(5):=('phone');    <span style="color: blue">  end if;</span>
<span style="color: blue">if </span>UPDATING <span style="color: blue">and :old</span>.jobs_id     != :<span style="color: blue">new</span>.jobs_id     <span style="color: blue">THEN </span>tab_VAR(6):=('jobs_id');   <span style="color: blue"> end if;</span>
<span style="color: blue">if </span>UPDATING <span style="color: blue">and :old</span>.salary      != :<span style="color: blue">new</span>.salary      <span style="color: blue">THEN </span>tab_VAR(7):=('salary');   <span style="color: blue">  end if;</span>
<span style="color: blue">if </span>UPDATING<span style="color: blue"> and :old</span>.departments_name   != :<span style="color: blue">new</span>.departments_name  <span style="color: blue">THEN </span>tab_VAR(8):=('departments_name'); <span style="color: blue">end if;
</span>

if DELETING and :old.first_name IS NOT NULL THEN tab_VAR(2):=('first_name'); end if;
if DELETING and :old.last_name IS NOT NULL THEN tab_VAR(3):=('last_name'); end if;
if DELETING and :old.email IS NOT NULL THEN tab_VAR(4):=('email'); end if;
if DELETING and :old.phone IS NOT NULL THEN tab_VAR(5):=('phone'); end if;
if DELETING and :old.jobs_id IS NOT NULL THEN tab_VAR(6):=('jobs_id'); end if;
if DELETING and :old.salary IS NOT NULL THEN tab_VAR(7):=('salary'); end if;
if DELETING and :old.departments_name IS NOT NULL THEN tab_VAR(8):=('departments_name'); end if;

INSERT INTO emp_monit VALUES (:new.id, user, systimestamp, v_oper, t_VARRAY(tab_VAR(1),tab_VAR(2),tab_VAR(3),tab_VAR(4),tab_VAR(5),tab_VAR(6),tab_VAR(7),tab_VAR(8)));
END;
/
show err

INSERT INTO employess(id,first_name,email,phone,jobs_id) VALUES (8,'k' ,'[email protected]',7869283,1);
DELETE FROM employess WHERE id=8;

SELECT id,user_name , oper_date ,oper_type FROM emp_monit;
select column_value from the(select CAST(oper_comment as t_VARRAY) from emp_monit where id=8);

TRIGGER / monitoring zmiany danych

INSERT INTO employess(id,first_name,email,phone,jobs_id) VALUES (8,'k' ,'[email protected]',7869283,1);
DELETE FROM employess WHERE id=8;

1 użytkowników online, w tym zalogowanych: 0, gości: 1

Praca dla programistów

Forum dyskusyjne

Sprawy administracyjne

O nas

Skontaktuj się z nami

TRIGGER / monitoring zmiany danych

INSERT INTO employess(id,first_name,email,phone,jobs_id) VALUES (8,'k' ,'[email protected]',7869283,1); DELETE FROM employess WHERE id=8;

1 użytkowników online, w tym zalogowanych: 0, gości: 1

INSERT INTO employess(id,first_name,email,phone,jobs_id) VALUES (8,'k' ,'[email protected]',7869283,1);
DELETE FROM employess WHERE id=8;