Home » SQL & PL/SQL » SQL & PL/SQL » TRIGGER
TRIGGER [message #209963] Mon, 18 December 2006 13:23 Go to next message
xxdanielexx
Messages: 7
Registered: October 2006
Junior Member
Hi guys, I have a problem. I need to block an INSERT, since I can't create a Unique Key on the table, I have to do it, by creating a TRIGGER, before the INSERT. What I do is simple, :

DECLARE

v_count NUMBER;

BEGIN
SELECT COUNT(*)
INTO v_count
FROM loft_pa_package_v
WHERE print_no = :new.print_no
AND mu_header_s_edition_id = :new.mu_header_s_edition_id
AND mu_header_s_language_id = :new.mu_header_s_language_id
AND mu_product_id = :new.mu_product_id
-- AND pa_package_print_nr_id = :new.pa_package_print_nr_id
AND is_trl='N';

IF v_count > 0 THEN
RAISE_application_error(-20101,'INSERT_NOT_ALLOWED');
ELSE
NULL;
END IF;
END;
Is this possible ? Do you think it can work ?
It doesnt' seem to work :
When I launch an insert the TRIGGER fires out an error...
It seems that it can read the table before the insert..Do you gusy have any other idea ?

Thanks..
Re: TRIGGER [message #209973 is a reply to message #209963] Mon, 18 December 2006 16:45 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you sure you did everything right? This example seems to be working properly:
SQL> create table test (id number, name varchar2(20));

Table created.

CREATE OR REPLACE TRIGGER trg_ins
  BEFORE INSERT ON TEST
  FOR EACH ROW
DECLARE
  l_cnt NUMBER;  
BEGIN
  SELECT COUNT(*) INTO l_cnt
    FROM TEST
    WHERE id = :NEW.id;
	
  IF l_cnt > 0 THEN 
     RAISE_APPLICATION_ERROR(-20101, 'Insert not allowed');
  END IF;
END;
/

SQL> insert into test (id, name) values (1, 'Littlefoot');

1 row created.

SQL> insert into test (id, name) values (1, 'Littlefoot');
insert into test (id, name) values (1, 'Littlefoot')
            *
ERROR at line 1:
ORA-20101: Insert not allowed
ORA-06512: at "SCOTT.TRG_INS", line 9
ORA-04088: error during execution of trigger 'SCOTT.TRG_INS'

SQL>
Re: TRIGGER [message #210021 is a reply to message #209963] Tue, 19 December 2006 00:21 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why can't you create a unique key on the table, while you can create a trigger?!
Previous Topic: compare
Next Topic: problem with DAY function..........
Goto Forum:
  


Current Time: Fri Dec 02 14:25:40 CST 2016

Total time taken to generate the page: 0.26345 seconds