SQL & PL/SQL

CREATE OR REPLACE TRIGGER xxnkmt_gl_interface_BI_1
BEFORE INSERT ON gl_interface
FOR EACH ROW
WHEN (NEW.USER_JE_CATEGORY_NAME = 'ReSA' AND NEW.USER_JE_SOURCE_NAME='ReSA')
declare
CURSOR dup_cur_int
( p_SET_OF_BOOKS_ID NUMBER
, p_ACCOUNTING_DATE DATE
, p_CURRENCY_CODE VARCHAR2
, p_USER_JE_CATEGORY_NAME VARCHAR2
, p_USER_JE_SOURCE_NAME VARCHAR2
, p_ENTERED_DR NUMBER
, p_ENTERED_CR NUMBER
, p_REFERENCE_1 VARCHAR2
, p_REFERENCE_2 VARCHAR2
, p_REFERENCE_3 VARCHAR2
, p_REFERENCE_4 VARCHAR2
, p_REFERENCE_5 VARCHAR2
, p_REFERENCE_21 VARCHAR2
, p_REFERENCE_22 VARCHAR2
, p_REFERENCE_23 VARCHAR2
, p_REFERENCE_24 VARCHAR2
, p_REFERENCE_25 VARCHAR2
, p_CODE_COMBINATION_ID NUMBER
, p_ACTUAL_FLAG VARCHAR2
, p_DATE_CREATED DATE
, p_STATUS VARCHAR2)
IS
SELECT count('x') rec_cnt
FROM GL_INTERFACE
WHERE SET_OF_BOOKS_ID = p_SET_OF_BOOKS_ID
AND ACCOUNTING_DATE = p_ACCOUNTING_DATE
AND CURRENCY_CODE = p_CURRENCY_CODE
AND USER_JE_CATEGORY_NAME = p_USER_JE_CATEGORY_NAME
AND USER_JE_SOURCE_NAME = p_USER_JE_SOURCE_NAME
AND ENTERED_DR = p_ENTERED_DR
AND ENTERED_CR = p_ENTERED_CR
AND NVL(REFERENCE1,'x') = NVL(p_REFERENCE_1,'x')
AND NVL(REFERENCE2,'x') = NVL(p_REFERENCE_2,'x')
AND NVL(REFERENCE3,'x') = NVL(p_REFERENCE_3,'x')
AND NVL(REFERENCE4,'x') = NVL(p_REFERENCE_4,'x')
AND NVL(REFERENCE5,'x') = NVL(p_REFERENCE_5,'x')
AND NVL(REFERENCE21,'x') = NVL(p_REFERENCE_21,'x')
AND NVL(REFERENCE22,'x') = NVL(p_REFERENCE_22,'x')
AND NVL(REFERENCE23,'x') = NVL(p_REFERENCE_23,'x') --In some times reference colums have null values ..so i put nvl function here
AND NVL(REFERENCE24,'x') = NVL(p_REFERENCE_24,'x')
AND NVL(REFERENCE25,'x') = NVL(p_REFERENCE_25,'x')
AND CODE_COMBINATION_ID = p_CODE_COMBINATION_ID
AND ACTUAL_FLAG = p_ACTUAL_FLAG
AND DATE_CREATED = p_DATE_CREATED
AND STATUS = p_STATUS;
L_CNT NUMBER;

BEGIN

OPEN dup_cur_int (:NEW.SET_OF_BOOKS_ID
, :NEW.ACCOUNTING_DATE
, :NEW.CURRENCY_CODE
, :NEW.USER_JE_CATEGORY_NAME
, :NEW.USER_JE_SOURCE_NAME
, :NEW.ENTERED_DR
, :NEW.ENTERED_CR
, :NEW.REFERENCE1
, :NEW.REFERENCE2
, :NEW.REFERENCE3
, :NEW.REFERENCE4
, :NEW.REFERENCE5
, :NEW.REFERENCE21
, :NEW.REFERENCE22
, :NEW.REFERENCE23
, :NEW.REFERENCE24
, :NEW.REFERENCE25
, :NEW.CODE_COMBINATION_ID
, :NEW.ACTUAL_FLAG
, :NEW.DATE_CREATED
, :NEW.STATUS);


FETCH dup_cur_int INTO l_cnt ;
CLOSE dup_cur_int;

IF L_CNT <> 0 THEN
:NEW.STATUS := 'DuplicateFromReSA';
dbms_output.put_line(l_cnt);
END IF;

EXCEPTION WHEN OTHERS THEN
NULL;
END;
/


Hello Friends,According to above trigger i want to eliminate the duplicate rows insertion in gl_interface table .i did the following actions to verify this trigger.
but it was faild.please tell me is there any errros in the code or any other way to get this requirement
.
I executed above trigger it executed without erros.
and i verified in toad for gl_interface talbe XXNKMT_GL_INT_DUPCHK_Trigger was enabled.

now I insert one row in the gl_interface table
insert into gl_interface (select * from gl_interface where REFERENCE21='5607426' AND REFERENCE22='TE_CASH_S' AND CODE_COMBINATION_ID=5589)
this select statement retrives only row.so another duplicate row inserted into the talbe now
after executing insert command i executed

select * from gl_interface where REFERENCE21='5607426' AND REFERENCE22='TE_CASH_S' AND CODE_COMBINATION_ID=5589

here i got the two rows (one original,another duplicate row)

but the status of duplicate row remains same..i,e status :='P' but according to the attached trigger the status must be 'DuplicateFromReSA'


I checked the gl_interface table.There is no unique(i.e sequence column )in the table .

Please guide me is there any erros in the code

Thanks for ur replay,
Now also status code is not updated for duplicate row..

Edited by: 805567 on Nov 8, 2010 7:39 AM