Home » RDBMS Server » Server Administration » dynamic trigger items
dynamic trigger items [message #370079] Mon, 11 December 2000 15:21 Go to next message
Howard Stein
Messages: 2
Registered: December 2000
Junior Member
I am trying to dynamically access the data in :old and :new fields without
having to use the literal :old.field and :new.field..

The trigger below shows 2 ways of calling the procedure p_addnote. The first, now
commented out, works by making a direct call of p_addnote with the literal data. This works.
In the second call I have called a second procedure that will dynamically create the call to p_addnote. This doesn’t work as I can’t access the :new or :old data.

Is it possible to dynamically get the data that is in the :new and :old field?

CREATE TRIGGER tr_update
BEFORE UPDATE
ON accounts
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_ntype users.notetype%TYPE;
Begin
SELECT notetype
INTO v_ntype
FROM users
WHERE code = lower(user);

IF :new.tb_company != :old.tb_company THEN
/*
p_addnote(:old.tb_accountid,'AUD',
'Company ''' || substr(:old.tb_company,1,29) ||''' changed by ' || lower(USER) || '.',
v_ntype,0);
p_addnote(:new.tb_accountid,'AUD',
'The new Company is ''' || substr(:new.company,1,29) ||'''' ,
v_ntype,0);

*/
p_updnote('tb_accountid','tb_company','AUD','Company',v_ntype);
END IF;
END tr_update;

CREATE OR REPLACE PROCEDURE p_updnote
(
p_accountid unotes_bcs.not_accountid%TYPE,
p_field VARCHAR2,
p_code unotes_bcs.not_notecode%TYPE,
p_text unotes_bcs.not_text%TYPE,
p_user unotes_bcs.not_user%TYPE
)
IS
v_newfield VARCHAR2(20);
v_oldfield VARCHAR2(20);
BEGIN
v_newfield := ':new.' || p_field;
v_oldfield := ':old.' || p_field;
p_addnote(p_accountid,p_code,p_text || '''' || v_oldfield || ''' changed by ' || lower(user) || '.',p_user);
p_addnote(p_accountid,p_code,'The new ' || p_text || 'is ''' || v_newfield || '''',p_user);
END p_updnote ;

PROCEDURE P_addnote
(
p_accountid unotes_bcs.not_accountid%TYPE,
p_code unotes_bcs.not_notecode%TYPE,
p_text unotes_bcs.not_text%TYPE,
p_user1 unotes_bcs.not_user%TYPE
)
IS
BEGIN
INSERT INTO tb_audit
(
not_accountid, not_dateofca, not_timeofca, not_text,
not_notecode, not_user, update_not, uptime_not, locked_not
)
VALUES
(
p_accountid, SYSDATE, p_text, p_code, p_user1
);
END;

Report message to a moderator

Re: dynamic trigger items [message #370089 is a reply to message #370079] Wed, 13 December 2000 08:48 Go to previous messageGo to next message
Shanthi Ramayanapu
Messages: 22
Registered: October 2000
Junior Member
I am not sure why you would like to do this, but here is the answer.

Insted of passing
p_updnote('tb_accountid','tb_company','AUD','Company',v_ntype);
pass the old and new values to p_updnote like
p_updnote(:oldtb_accountid, :new.tb_accountsid .....)

and change your procedure accordingly to accept new and old values as parameters.
Old and new values are unavailable outside the trigger code. They lose visibility so you need to pass them as paramaters if you have to use
them.

Shanthi

Report message to a moderator

Re: dynamic trigger items [message #370093 is a reply to message #370079] Wed, 13 December 2000 09:33 Go to previous message
Howard Stein
Messages: 2
Registered: December 2000
Junior Member
Thanks. That's what I came up with. A good reason for doing this is so that the compare of :old versus :new is in one statement rather then multiple statments. This makes for easier understanding of the program.

CREATE TRIGGER tr_update
BEFORE UPDATE
ON accounts
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_ntype users.notetype%TYPE;
Begin
SELECT notetype
INTO v_ntype
FROM users
WHERE code = lower(user);
p_updnote('tb_accountid',':new.tb_company',':old.tb_company’AUD','Company',v_ntype);
p_updnote('tb_accountid',':new.tb_address1',':old.tb_ address2’AUD','Address 1',v_ntype);
p_updnote('tb_accountid',':new.tb_address2',':old.tb_ address2’AUD','Address 2',v_ntype);
p_updnote('tb_accountid',':new.tb_city',':old.tb_city’AUD','City',v_ntype);
p_updnote('tb_accountid',':new.tb_state',':old.tb_state’AUD','State',v_ntype);
p_updnote('tb_accountid',':new.tb_zip',':old.tb_zip’AUD','Zip',v_ntype);
END tr_update;

CREATE OR REPLACE PROCEDURE p_updnote
(
p_accountid unotes_bcs.not_accountid%TYPE,
p_newfield VARCHAR2,
p_oldfield VARCHAR2,
p_code unotes_bcs.not_notecode%TYPE,
p_text unotes_bcs.not_text%TYPE,
p_user unotes_bcs.not_user%TYPE
)
IS
BEGIN
IF p_newfield != p_oldfield THEN
p_addnote(p_accountid,p_code,p_text || '''' || p_oldfield || ''' changed by ' || lower(user) || '.',p_user);
p_addnote(p_accountid,p_code,'The new ' || p_text || 'is ''' || p_newfield || '''',p_user);
END IF
END p_updnote ;

PROCEDURE P_addnote
( p_accountid unotes_bcs.not_accountid%TYPE,
p_code unotes_bcs.not_notecode%TYPE,
p_text unotes_bcs.not_text%TYPE,
p_user1 unotes_bcs.not_user%TYPE)
IS
BEGIN
INSERT INTO tb_audit
( not_accountid, not_dateofca, not_text, not_notecode, not_user )
VALUES
( p_accountid, SYSDATE, p_text, p_code, p_user1);
END;

Report message to a moderator

Previous Topic: Re: WHERE column_name IN variable_name
Next Topic: Re: returning binaries or blobs in ref_cursors
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 02 01:58:41 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.