Does anyone have a suggestion on how I get an INSTEAD OF trigger to work with a CLOB? Code and error posted below...
Thanks in advance for any help!
Cheers,
Keith.
Code:-- create trigger CREATE OR REPLACE TRIGGER upd_fip_news INSTEAD OF UPDATE ON v_fip_news FOR EACH ROW BEGIN UPDATE v_fip_news SET news_headln = :NEW.news_headln, news_body = :NEW.EMPTY_CLOB(), news_desc = :NEW.news_desc, news_grp_id = :NEW.news_grp_id, news_created_usr = :NEW.news_created_usr, news_modified_usr = :NEW.news_modified_usr, news_created_dt = :NEW.news_created_dt, news_modified_dt = :NEW.news_modified_dt, news_eff_dt = :NEW.news_eff_dt, news_exp_dt = :NEW.news_exp_dt, news_grp_lock = :NEW.news_grp_lock WHERE news_id = :NEW.news_id AND news_lang = :NEW.news_lang RETURNING news_body INTO :NEW.news_body; IF SQL%ROWCOUNT = 0 THEN BEGIN INSERT INTO v_fip_news (news_id, news_lang, news_headln, news_body, news_desc, news_grp_id, news_created_usr, news_modified_usr, news_created_dt, news_modified_dt, news_eff_dt, news_exp_dt, news_grp_lock) VALUES (:NEW.news_id, :NEW.news_lang, :NEW.news_headln, :NEW.EMPTY_CLOB() :NEW.news_desc, :NEW.news_grp_id, :NEW.news_created_usr, :NEW.news_modified_usr, :NEW.news_created_dt, :NEW.news_modified_dt, :NEW.news_eff_dt, :NEW.news_exp_dt, :NEW.news_grp_lock); END; END IF; END; Warning: Trigger created with compilation errors. Errors for TRIGGER UPD_FIP_NEWS: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/30 PLS-00049: bad bind variable 'NEW.EMPTY_CLOB' 38/15 PLS-00049: bad bind variable 'NEW.EMPTY_CLOB' 39/15 PLS-00103: Encountered the symbol "" when expecting one of the following: . ( ) , * % & | = - + < / > at in mod not range rem => ..<> or != or ~= >= <= <> and or like between is null is not || is dangling The symbol "(" was substituted for "" to continue. 47/34 PLS-00103: Encountered the symbol ";" when expecting one of the following: LINE/COL ERROR -------- ----------------------------------------------------------------- . ( ) , * % & | = - + < / > at in mod not range rem => .. <> or != or ~= >= <= <> and or like between is null is not || is dangling The symbol ")" was substituted for ";" to continue.




Reply With Quote