Re: Can this be done with a trigger?

artmerar_at_yahoo.com wrote:
: On Aug 5, 11:43 am, gazzag <gar..._at_jamms.org> wrote:
: > On 5 Aug, 17:11, artme..._at_yahoo.com wrote:
: >
: >
: >
: > > Hi,
: >
: > > We have a table like this:
: >
: > > CUSTOMER_ID
: > > PRODUCT_ID
: > > FORMAT
: > > STATUS
: > > DATE_ADDED
: > > DATE_MODIFIED
: >
: > > We also have a history table like this:
: >
: > > CUSTOMER_ID
: > > PRODUCT_ID
: > > FORMAT
: > > STATUS
: > > SOURCE
: > > DATE_ADDED
: >
: > > We want to put a trigger on the first table such that when a record is
: > > inserted or updated it will make the entry to the history table.
: > > However, the problem is the 'SOURCE' column on the history table. It
: > > is not present or needed in the first table.
: >
: > > Entries into the first table are done through a PL/SQL program. So,
: > > what we want is that when the entry is made into the first table, it
: > > fires the trigger to make the second entry. But, how can this be done
: > > if the columns do not match?
: >
: > > We do not want to code it in the PL/SQL program because then someone
: > > can make an entry to the first table and we cannot record the history
: > > on it, yet the SOURCE column only gets populated in the PL/SQL
: > > program....
: >
: > > And, to make things more complicated, depending on what is being
: > > UPDATED, we may need the values of :NEW or :OLD........
: >
: > > Is there a way to do this?
: >
: > Sure there is. What is SOURCE though?

: Gazzag,

: I am trying to write the trigger. Problem is since you cannot pass
: parameters to triggers, where does the trigger get the value for
: SOURCE?
What is supposed to go into SOURCE, and how do you know what the value is?

What ever it is, you have to set it up before hand.

Anyway, if for example you want the name of the pl/sql procedure that contains the sql statements, then perhaps something like this (very bad code, just quick examples...)

	procedure foobag (eg1 number, eg2 number ) is
	begin
		my_utils.which_proc('foobag');

		insert into blazzer (one,two) values (eg1, eg2);
	end;


	create package [body] my_utils -- need a header and a body
	which_proc varchar2(70);
	procedure which_proc( p ) is
	begin
		which_proc := p;
	end;
	end my_utils;


	create trigger blazzer_trigger on blazzer
	begin
		insert into blazzer_log
		(one,two,source)
		values
		(:new.one,:new.two, my_utils.which_proc);
	end;