| URGENT:-To write Trigger on a table with Column of LONG Datatype [message #84119] |
Sat, 31 January 2004 23:49 |
amit sharma
Messages: 9
Registered: October 2002
|
Junior Member |
|
|
hi ,
I am using a long column in a table CLA_AUDIT_TRAIL.
It has a column TRAIL_DN of type LONG (as i need to insert data of 11000 characters)
I need to write a AFTER INSERT/update/delete trigger on THIS TABLE , which will insert data into a history table CLH_AUDIT_TRAIL which is also having column TRAIL_DN of type LONG.
but the trigger doesn't fire because of this error
ORA-04093: references to columns of type LONG are not allowed in triggers.
I NEED TO WRITE INTO HISTORY TABLE AND ALSO NEED A DATATYPE WHICH CAN ACCOMODATE 11000 CHARACTERS.
KINDLY ADVISE URGENTLY WHAT I CAN DO -
I am pasting the code below ->>>>>>>
#####################
CREATE OR REPLACE TRIGGER clt_h_audit_trail_u
AFTER UPDATE
ON cla_audit_trail
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
CLP_H_AUDIT_TRAIL(:NEW.PART_NR, :NEW.USER_ID_NO,
:NEW.ADD_DT, :NEW.VAL_CUST, :NEW.VAL_EXP,
:NEW.TRAIL_DN, 'U');
END; -- TRIGGER
#####################
PROCEDURE CLP_H_AUDIT_TRAIL --
-- INSERTS changes on table CLA_AUDIT_TRAIL
-- into history CLH_AUDIT_TRAIL
--
(PART_NR IN CLA_AUDIT_TRAIL.PART_NR%TYPE,
USER_ID_NO IN CLA_AUDIT_TRAIL.USER_ID_NO %TYPE,
ADD_DT IN CLA_AUDIT_TRAIL.ADD_DT%TYPE,
VAL_CUST IN CLA_AUDIT_TRAIL.VAL_CUST%TYPE,
VAL_EXP IN CLA_AUDIT_TRAIL.VAL_EXP%TYPE,
TRAIL_DN IN CLA_AUDIT_TRAIL.TRAIL_DN%TYPE,
CHG_CD IN CLH_AUDIT_TRAIL.CHG_CD%TYPE)
AS
BEGIN
if (rtrim(chg_cd) = 'U') then
nui_debug('CLP_H_AUDIT_TRAIL', chg_cd || ', PN:' || rtrim(part_nr) || ', UID:' ||
rtrim(user_id_no) );
end if;
INSERT INTO CLH_AUDIT_TRAIL
----------------------------------
(PART_NR, USER_ID_NO, ADD_DT, VAL_CUST, VAL_EXP, TRAIL_DN, CHG_DT, CHG_CD)
----------------------------------
VALUES
(PART_NR, USER_ID_NO, ADD_DT, VAL_CUST, VAL_EXP, TRAIL_DN, SYSDATE, CHG_CD);
END; -- Procedure
Thanks
Amit
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
