I am executing a sql query which will update the local table and upon update a trigger will fire to update the remote SQL server db table using DBlink.
SQL> update ps_len_tmp set nd_rank_title = 'COL' where emplid = '0028379';
update ps_len_tmp set nd_rank_title = 'COL' where emplid = '0028379'
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Transparent gateway for MSSQL]
ORA-02063: preceding 2 lines from TG4MSQL
ORA-06512: at "ADM.LENINSERT", line 6
ORA-04088: error during execution of trigger 'ADM.LENINSERT'
> DBlink is working
> ODBC connection is fine
> trigger is valid and working
application guys point this to db related error
where to start trouble shoot?
CREATE OR REPLACE TRIGGER ADM.LENINSERT
AFTER INSERT OR UPDATE
FOR EACH ROW
IF :new.badge_expirdt is not null
and :new.badge_categry is not null
and :new.badge_categry <> ' ' THEN
INSERT INTO UDO_TEMP@tg4msql
when the condition is met the trigger will fire, other wise the local update should happen which is failing.
It may be that you need to see if new.badge_categry is 'empty'. Which would be something like and :new.badge_categry <> '', which in my limited understanding is not the same as null.
You are right Dbtoo, this column value when null or empty has to be satisfied for the trigger to fire, but the normal dml on the local table is not happening pertaining to the local table only execution as well all the satisfying conditions listed in the trigger.