Can this be done with a trigger?
10/02/2019 11:47
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_IDPRODUCT_IDFORMATSTATUSSOURCEDATE_ADDED We want to put a trigger on the first table such that when a record isinserted or updated it will make the entry to the history table.

However, the problem is the 'SOURCE' column on the history table. Itis 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, itfires the trigger to make the second entry. But, how can this be doneif the columns do not match? We do not want to code it in the PL/SQL program because then someonecan make an entry to the first table and we cannot record the historyon it, yet the SOURCE column only gets populated in the PL/SQLprogram....

And, to make things more complicated, depending on what is beingUPDATED, we may need the values of :NEW or :OLD........

Is there a way to do this?

Source is Usenet: comp.databases.oracle.misc
Sign in to add a comment

Answer score: 5
10/02/2019 11:47 - SOURCE will have a default value if not specified. I guess my mainquestion is, can I somehow populate source for the trigger to handlewhen SOURCE is not in the triggering table....??

Source is Usenet: comp.databases.oracle.misc
Sign in to add a comment

Answer score: 5
10/02/2019 11:47 - And if someone inserts a row in the table without using the PL/SQL program(the scenario you were worried about above), how does SOURCE get populated?The trigger has to be able to assume a value for SOURCE, or this will notwork.


Source is Usenet: comp.databases.oracle.misc
Sign in to add a comment

Answer score: 5
10/02/2019 11:47 - Sure there is. What is SOURCE though?

Source is Usenet: comp.databases.oracle.misc
Sign in to add a comment

Answer score: 5
10/02/2019 11:47 - Gazzag, I am trying to write the trigger. Problem is since you cannot passparameters to triggers, where does the trigger get the value forSOURCE? All I can think of is to update the record from the PL/SQLprocedure once the trigger gets done........

So, say I have a procedure like this: PROCEDURE ADD ( p_customer_id NUMBER, p_product_id VARCHAR2, p_source VARCHAR2 DEFAULT 'U') IS INSERT INTO customer_config VALUES (p_customer_id, p_product_id, 'Text', 'Active', SYSDATE); The SOURCE is not needed in this table. But, this would fire thetrigger where SOURCE is needed. The SOURCE will be derived within thePL/SQL code. I do not think this is possible. I may have to let thetrigger fire, then go back and update the row.....


Source is Usenet: comp.databases.oracle.misc
Sign in to add a comment

eDiscover
Helpforce eDiscover provides technical articles updated each dayHelpforce eDiscover RSS feed contains the latest technical articles in RSS
Click the logo to go back to the main page
Search eDiscover
  
Categories

Click an icon to go to that category

Helpforce eDiscover contains articles about Microsoft Windows Helpforce eDiscover contains articles about Apple products and MacOS Helpforce eDiscover contains articles about Linux and POSIX operating systems Helpforce eDiscover contains articles about Helpforce Helpforce has a large variety of technical information and articles for you to read Helpforce eDiscover contains articles about databases, MYSQL, SQL Server Oracle Helpforce eDiscover contains articles about Java, JVM and the JRE Helpforce eDiscover contains articles about the QNX operating system Helpforce eDiscover contains articles about Oracle Solaris and Open Solaris Helpforce eDiscover contains articles about RISC OS, Acorn and the BBC Micro Helpforce eDiscover contains articles about Amiga and AmigaOS

Type your comment into the box below