SQL & PL/SQL

Hi,

Another problem with trigger.
What I want to do is check if value exist in table before insert.
If value exists then ERROR
if value not exists then INSERT.

desc target_table;

Name              Null     Type          
----------------- -------- ------------- 
TEST_ID           NOT NULL NUMBER        
TE_TESTURE_CD              VARCHAR2(100) 

trigger:

CREATE OR REPLACE TRIGGER te_testure_cd_check
BEFORE INSERT OR UPDATE
ON target_table
FOR EACH ROW
DECLARE
	l_count NUMBER;
BEGIN
	IF :new.te_testure_cd IS NOT NULL THEN
		SELECT	COUNT (1)
		INTO	l_count
		FROM	target_table
		WHERE	test_id = :new.test_id
		AND	te_testure_cd = :new.te_testure_cd;

		IF l_count > 0 THEN
			RAISE_APPLICATION_ERROR(-20005, 'Duplicate key');
		END IF;
	END IF;
END;

there is going to be multiple NULL columns:

--1
INSERT INTO target_table VALUES (201112, NULL); --OK
INSERT INTO target_table VALUES (201112, NULL); --OK
INSERT INTO target_table VALUES (201112, 'TEST1'); --OK
--2
INSERT INTO target_table VALUES (201201, NULL); --OK
INSERT INTO target_table VALUES (201201, NULL); --OK
INSERT INTO target_table VALUES (201201, 'TEST1'); --OK
--not wanted
INSERT INTO target_table VALUES (201201, 'TEST1'); --ERROR

SQL> define
DEFINE _DATE           = "11-JAN-11" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "test" (CHAR)
DEFINE _USER           = "test" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000200" (CHAR)

Edited by: slkLinuxUser on Jan 24, 2012 9:02 PM