Trigger that checks if the value exists in another table before insert
695546Apr 13 2009 — edited Apr 13 2009Hi,
Can any one help me with the correct code.
I am trying to write a trigger that checks if the new value (:new.T1) does not exist in another table (T2) before entering it (in T1).
Create or replace trigger T1
BEFORE
insert on T1
for each row
Declare
x Number;
begin
SELECT Count (*) INTO x
From T2 WHERE T2.Col1 = :new.T1;
IF (x > 1 or x = 1) Then
DBMS_OUTPUT.PUT_LINE('This value has been used before try another one.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO T2 (Col1) values (:new.T1);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('This value has been used before try another one.');
end;
Edited by: user1795671 on Apr 13, 2009 6:53 AM
I accidently posted the question in this forum I think it belongs to the SQl and PL/SQL. I will post it there.