Hello!

I need to create one, or three, triggers to populate my event log table
to integrate an Oracle database with IDM. The problem is that the source
of data is a view and I need to create the triggers. When I try to
create, using the same script for other 04 JDBC drivers, I got an error
about use INSTEAD OF INSERT instead INSERT statement.

I break the script in 03. One for each operation: insert, update and
delete. All triggers were created but when I change anything in table,
that reflects the update in the view, nothing happens. This is my
triggers:

INSERT:

CREATE OR REPLACE TRIGGER tgr_idm_i_crm
INSTEAD OF INSERT
ON idm_vantive
FOR EACH ROW
BEGIN
INSERT INTO idm.idm_evtcrm
(
record_id,
table_key,
event_type,
event_time,
table_name
)
VALUES
(
IDM_SEQ_LOG_RECORD_ID.nextval,
('pk_idm_login=' || :NEW.login),
5,
sysdate,
'idm_vantive'
);

END tgr_idm_i_crm;

UPDATE:

CREATE OR REPLACE TRIGGER tgr_idm_u_crm
INSTEAD OF UPDATE
ON idm_vantive
FOR EACH ROW
BEGIN

INSERT INTO idm.idm_evtcrm
(
record_id,
table_key,
event_type,
event_time,
table_name
)
VALUES
(
IDM_SEQ_LOG_RECORD_ID.nextval,
('pk_idm_login=' || :OLD.login),
6,
sysdate,
'idm_vantive'
);

END tgr_idm_u_crm;

DELETE:

CREATE OR REPLACE TRIGGER tgr_idm_d_crm
INSTEAD OF DELETE
ON idm_vantive
FOR EACH ROW
BEGIN

INSERT INTO idm.idm_evtcrm
(
record_id,
table_key,
event_type,
event_time,
table_name
)
VALUES
(
IDM_SEQ_LOG_RECORD_ID.nextval,
('pk_idm_login=' || :OLD.login),
4,
sysdate,
'idm_vantive'
);

END tgr_idm_d_crm;

*idm_evtcrm is my event log table.

My scripts are wrong?

Thank you!


--
* Alan Cota | Open Consult | Brazil, Novell Platinum Partner.
CNE | ISM & Security Specialist.
http://www.alancota.net*
------------------------------------------------------------------------
AlanCota's Profile: http://forums.novell.com/member.php?userid=1961
View this thread: http://forums.novell.com/showthread.php?t=429749