• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1045
  • Last Modified:

Database Tigger

The reason that I had been asking about the use of the variable ":old.broker_analyst_code".
For various reasons, I am having difficulty understanding how or why Oracle would associated
":old.broker_analyst_code".
based on select statement below, what is the meaning of using ":new" in oracle.
SELECT rowid,
NVL(refresh_date,report_date),
NVL(media_flag,0),
terminated_date
INTO v_rowid,
v_report_date,
v_media_flag,
v_terminated_date
FROM Daily_Report
WHERE identifier =:new.identifier
AND master =:new.master
AND broker =:new.broker
AND period_type =:new.period_type
AND period =:new.period
AND value =:new.value
AND NVL(refresh_date,report_date)=(select max(NVL(refresh_date,report_date)) FROM Daily_Report
WHERE WHERE identifier =:new.identifier
AND master =:new.master
AND broker =:new.broker
AND period_type =:new.period_type
AND period =:new.period);
The record in DAILY_REPORT that is obtained by this query And i am having trouble understanding what that would be ":new.broker_analyst_code = :old.broker.analyst_code"
how to work these
:new.broker_analyst_code = :old.broker.analyst
:new.broker_analyst_code =:old.broker_analyst_code
:new.broker_analyst_code <> :old.broker_analyst_code
and what is stand for ":new." AND ":old."
I AM ALSO GIVE YOU TRIGGER AS AN ATTACHEMENT.
meaning of thish in given code(TRIGGER)
TRIGGER:
create or replace
TRIGGER AFTER_DAILY_ESTIMATES
AFTER INSERT ON DAILY_ESTIMATES
FOR EACH ROW
WHEN(new.value > 0 OR new.value = -999)
DECLARE
v_rowid  ROWID;
v_value  NUMBER;
v_report_date  DATE;
v_earned_period DATE;
v_err_number NUMBER;
v_err_text VARCHAR2(200);
v_media_flag NUMBER;
v_terminated_date DATe;
no_dupl_found EXCEPTION;
PRAGMA EXCEPTION_INIT (no_dupl_found,100);
BEGIN
IF :new.identifier ='SA' THEN
SELECT MAx(period)
INTO v_earned_period
FROM earnings_data_new
WHERE master = :new.master;
END IF;
IF(:new.identifier ='SA' AND v_earned_period < last_day(to_date(:new.period,'yymm')))
OR(:new.identifier='SA' AND v_earned_period IS NULL)
OR :new.identifier ='TP' THEN
BEGIN
SELECT rowid,
value
into v_rowid,
v_value
FROM Daily_Report
WHERE identifier = :new.identifier
AND master = :new.master
AND broker = :new.broker
ANd period = :new.period
AND period_type = :new.period_type
AND NVL(refresh_date,report_date) = :new.report_date;
IF v_value = :new.value THEN
-- Not inserting any dupl.records
NULL
ELSIF :new.value= -999 THEN
--Update records in Daily_Report n/a value
UPDATE Daily_Report
SET value = -999,
date_updated = sysdate
WHERE rowid = v_rowid
ELSE
--Remove new records and records from Daily_Report table
--if value is different for the same report_date
INSERT INTO Estimates_Err_Report
SELECT action,
identifier,
master,
broker,
broker_analyst_code,
period_type,
period,
value,
rating_code,
ltg_rate,
currency_code,
dilution,
report_date,
refresh_date,
refresh_entered_date,
terminated_date,
terminated_entered_date,
date_appended,
curser,
date_updated,
sysdate,
media_flag
FROM Daily_Report
WHERE rowid = v_rowid;
INSERT INTO Estimates_Err_Report
Values(:new.action,
:new.identifier,
:new.master,
:new.broker,
:new.broker_analyst_code,
:new.period_type,
:new.period,
:new.value,
:new.rating_code,
:new.ltg_rate,
:new.currency_code,
:new.dilution,
:new.report_date,
:new.date_entered,
:new.referesh_date,
:new.refresh_entered_date,
:new.terminated_date,
:new.terminated_entered_date,
:new.date_appended,
:new.curuser,
sysdate,
null
:new.media_flag);
DELETE FROM Daily_Report
WHERE rowid = v_rowid;
END IF;
ExCEPTION
THEN no_dupl_found THEN
--Then checking if the records can be refreshed.This means if the sales
--value for current new record with the same set can be found
DECLARE no_such_value EXCEPTION;
PRAGMA EXCEPTION_INIT(no_such_value,100);
BEGIN
SELECT rowid,
NVL(refresh_date,report_date),
NVL(media_flag,0),
terminated_date
INTO v_rowid,
v_report_date,
v_media_flag,
v_terminated_date
FROM Daily_Report
WHERE identifier = :new.identifier
AND master =:new.master
AND broker =:new.broker
AND period_type =:new.period_type
AND period =:new.period
AND value =:new.value
AND NVL(refresh_date,report_date))
FROM Daily_Report
WHERE identifier =:new.identifier
AND master =:new.master
AND broker =:new.broker
AND period_type = :new.period_type
AND period = :new.period);
--IF NO_DATA_FOUND then see below Exception no_such_value
--Updating refresh_date and refresh_entered_date if sales value
--is same for this set
IF :new.report_date > v_report_date
AND v_terminated_date is null
AND :new.broker_analyst_code = :old.broker.analyst_code THEN
IF v_media_flag =0 and :new.media_flag!=0 THEN
v_media_flag:=:new.media_flag;
END IF
UPDATE Daily_Report
SET refresh_date =:new.report_date,
refresh_entered_date =:new.date_entered,
date_updated =:sysdate,
media_flag = v_media_flag
WHERE rowid = v_rowid;
ELSIF :new.report_date > v_report_date
AND v_terminated_date is not null
AND v_terminated_date > v_report_date
AND :new.broker_analyst_code =:old.broker_analyst_code THEN
UPDATE daily_report
SET refresh_date =:new.report_date
WHERE rowid = v_rowid;
ELSEIF :new.report_date > v_report_date
AND v_terminated_date is null
AND :new.broker_analyst_code <> :old.broker_analyst_code THEN
UPDATE daily_report
SET error_code =14
WHERE rowid = v_rowid;
INSERT INTO Daily_Report(action,
identifier,
broker,
master,
period,
period_type,
broker_analyst_code,
value,
rating_code,
ltg_rate,
currency_code,
dilution,
report_date,
date_entered,
refresh_date,
refresh_entered_date,
terminated_date,
terminated_entered_date,
date_appended,
curser,
date_updated,
error_code,
media_flag)
VALUES(:new.action,
:new.identifier,
:new.broker,
:new.master,
:new.period,
:new.period_type,
:new.broker_analyst_code,
:new.value,
:new.rating_code,
:new.ltg_rate,
:new.currency_code,
:new.dilution,
:new.report_date,
:new.date_entered,
:new.refresh_date,
:new.refresh_entered_date,
:new.terminated_date,
:new.terminated_entered_date,
:new.date_appended,
:new.curser,
sysdate,
15,
:new.media_flag);
ELSE
INSERT INTO Daily_Report(action,
identifier,
broker,
master,
period,
period_type,
broker_analyst_code,
value,
rating_code,
ltg_rate,
currency_code,
dilution,
report_date,
date_entered,
refresh_date,
refresh_entered_date,
terminated_date,
terminated_entered_date,
date_appended,
curser,
date_updated,
error_code,
media_flag)
VALUES(:new.action,
:new.identifier,
:new.broker,
:new.master,
:new.period,
:new.period_type,
:new.broker_analyst_code,
:new.value,
:new.rating_code,
:new.ltg_rate,
:new.currency_code,
:new.dilution,
:new.report_date,
:new.date_entered,
:new.refresh_date,
:new.refresh_entered_date,
:new.terminated_date,
:new.terminated_entered_date,
:new.date_appended,
:new.curser,
sysdate,
2,
:new.media_flag);
END IF;

Open in new window

TRIGGER.NEW.doc
0
Pradeep_Tiwari
Asked:
Pradeep_Tiwari
  • 6
  • 6
1 Solution
 
sdstuberCommented:
In an after row trigger like you have..

:old  refers to the row before the operation occured
:new refers to the row after the operation occured.

For an INSERT,  there was no "old" record, so it will be NULL

:new refers to the row being created.
0
 
Pradeep_TiwariAuthor Commented:
GIVE me some details taht is used in DATABASE TRIGGER.
LIKE,
:new.broker_analyst_code = :old.broker.analyst
:new.broker_analyst_code =:old.broker_analyst_code
:new.broker_analyst_code <> :old.broker_analyst_code
0
 
sdstuberCommented:
None of those conditions make any sense.

It's comparing the new value that has just been inserted, to an old row that doesn't exist (because it hadn't been inserted yet)

So, for example this condition...
    AND :new.broker_analyst_code = :old.broker.analyst_code THEN

will always be FALSE,  because No new value can ever equal NULL (the old will be NULL)
Even if the NEW is NULL,  NULL is not equal to NULL

Using both NEW and OLD only applies in an update trigger.

For an insert there is only NEW
For a delete there is only OLD

So, I'd say that trigger is buggy, while the syntax is legal, it simply doesn't make sense to look for "old" data when you already know it won't exist.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Pradeep_TiwariAuthor Commented:
give me one example on how to work :old and :new in database trigger.
i have table EMPLOYEE_MAST with coloumn name
EMP_ID, EMP_NAME,EMP_ADDRESS with some old data
0001 ,Arindam Saha,16A Arabindanagar, Kolkata-47
0002,Sumit Kundu,Bally
0003,Nairit,Selimpur Road
0004,Suryashis,BT Road

Give me trigger on this table describe :new and :old concept
0
 
sdstuberCommented:
first, it will have to be an UPDATE trigger as only an update will have both an old and a new.

So, my update trigger will check to see if the employee id has changed and if so, I'll raise an error and say you are not allowed to do that.

I'll also check to see if the name and the address have been left unchanged.  If so, I won't raise an exception, but I'll send a message that the
update isn't really doing anything for that row.
CREATE OR REPLACE TRIGGER trg_employee_mast_BRU
    BEFORE UPDATE
    ON EMPLOYEE_MAST
    FOR EACH ROW
BEGIN
   IF :old.emp_id != :new.emp_id THEN
        raise_application_error(-20001,'You may not update the employee id');
   END IF;
 
  IF :old.emp_name = :new.emp_name and :old.emp_address = :new.emp_address THEN
        dbms_output.put_line('This update did not change anything for ' || :old.emp_id);
  END IF;
END;

Open in new window

0
 
Pradeep_TiwariAuthor Commented:
OK.
on above given my tigger
TRIGGER AFTER_DAILY_ESTIMATES
AFTER INSERT ON DAILY_ESTIMATES
FOR EACH ROW

If I understand you correctly, you are saying that---unless the trigger declares otherwise, ":old" and ":new" refer to fields in the record that is inserted in DAILY_ESTIMATES.   This is consistent with what I had thought.   Both the "newly updated record" and the "older image of the newly updated record" refer to
the record that has been inserted into DAILY_ESTIMATES.  
 
Here is my thinking on this (and (A) and (B) are probably redundant with what you wrote; sorry..):
 
(A) the trigger in question (AFTER_DAILY_ESTIMATES) is declared, initially, as a trigger "AFTER INSERT ON DAILY_ESTIMATES".
 
My understanding here is that this trigger is executed with respect only to one specific record in the table DAILY_ESTIMATES,
that has just been inserted in the table DAILY_ESTIMATES.  
 
(B) I think that for insert triggers, any reference using default table-field-name prefixes ":old" and ":new" would necessarily be references to the table that the trigger is based on (or would be based on, unless there were any explicit declarations, stating that ":old" and/or ":new" would be used to refer to some table _other than the table that the trigger is based on.    I do not see any declarations in this trigger that define ":old" or ":new" as being anything other than their default orientation, which would be to refer to the table that the trigger is based on.  And the trigger is based on DAILY_ESTIMATES.   So I think that both ":old" and ":new" refer to values in DAILY_ESTIMATES.  
 
(C)  It might be possible to suggest a counter argument:   "well, yes, the table is based on DAILY_ESTIMATES, but it inserts into DAILY_REPORT.".
 
Yes, this is true.  The trigger inserts into DAILY_REPORT.  It ALSO inserts into ESTIMATES_ERR_REPORT.  And for both of these tables, it inserts the value
":new.broker_analyst_code" into the table.   So I would think that if Oracle really thought that ":old" and ":new" were referring to anything other than DAILY_ESTIMATES, then there might be some confusion as to whether ":old" and ":new" were referring to ESTIMATES_ERR_REPORT, or to DAILY_REPORT.
 
(D)   The reason that this matters is because the variable (in the TRIGGER) ":old.broker_analyst_code" is SUPPOSED to be the broker_analyst_code
that is associated with a record in DAILY_REPORT that is returned as the result of the following query:
 
            SELECT rowid
                NVL (refresh_date, report_date)
                NVL (media_flag, 0)
                terminated_date
            INTO   v_rowid
                v_report_date,
                    v_media_flag
                    v_terminated_date
            FROM  Daily_Report
            WHERE  identifier = :new.identifier
            AND master = :new.master
            AND broker = :new.broker
            AND period_type = :new.period_type
            AND period = :new.period
            AND value = :new.value
            AND NVL (refresh_date, report_date) =
                (SELECT max(NVL(refresh_date, report_date))
                FROM Daily_Report
                WHERE identifier = :new.identifier
                AND  master = :new.master
                AND  broker =  :new.broker
                AND period_type  = :new.period_type
                AND period = :new.period)
 
OKAY.   Now, that query will generally produce a record; and the record it produces will be a record from DAILY_REPORT.
 
But what I do NOT see in this query is ANY reference to "broker_analyst_code" (whether "old" or "new").  
So I do NOT think that ORACLE is associating ":old.broker_analyst_code"
with the value of "broker_analyst_code" that is in whatever record would be produced by THIS query.
 
I think that the value of ":old.broker_analyst_code" is something else.   Not sure what.  It could be undefined,
or it could be the value of "broker_analyst_code" in the record that was just inserted into DAILY_ESTIMATES.
 
I agree that the record in DAILY_ESTIMATES is the major determinant of the query to DAILY_REPORT.
But--with respect to this query--the function of the record from DAILY_ESTIMATES
is to provide values that will be used as arguments for a query
that is used in order to get a record from DAILY_REPORT.
The record that is obtained by this query is a record from DAILY_REPORT.  
It is not a record from DAILY_ESTIMATES.
The value of the field "broker_analyst_code" from the record in DAILY_REPORT may, or may NOT, be the same
as the value of the field "broker_analyst_code" in the record from DAILY_ESTIMATES.
 
The point of the query is that we don't know that they are the same,
but we would like to check to see if they are the same.
But in order to check to see if they are the same,
we actually have to compare the value of the "broker_analyst_code" from the record from DAILY_REPORT,
with the value of "broker_analyst_code" from the record in DAILY_ESTIMATES.
And I don't think the logic in the trigger is doing that.
 
please let me know your thoughts on this
and if bug then how to correct it.

TRIGGER:
create or replace
TRIGGER AFTER_DAILY_ESTIMATES
AFTER INSERT ON DAILY_ESTIMATES
FOR EACH ROW
WHEN(new.value > 0 OR new.value = -999)
DECLARE
v_rowid  ROWID;
v_value  NUMBER;
v_report_date  DATE;
v_earned_period DATE;
v_err_number NUMBER;
v_err_text VARCHAR2(200);
v_media_flag NUMBER;
v_terminated_date DATe;
no_dupl_found EXCEPTION;
PRAGMA EXCEPTION_INIT (no_dupl_found,100);
BEGIN
IF :new.identifier ='SA' THEN
SELECT MAx(period)
INTO v_earned_period
FROM earnings_data_new
WHERE master = :new.master;
END IF;
IF(:new.identifier ='SA' AND v_earned_period < last_day(to_date(:new.period,'yymm')))
OR(:new.identifier='SA' AND v_earned_period IS NULL)
OR :new.identifier ='TP' THEN
BEGIN
SELECT rowid,
value
into v_rowid,
v_value
FROM Daily_Report
WHERE identifier = :new.identifier
AND master = :new.master
AND broker = :new.broker
ANd period = :new.period
AND period_type = :new.period_type
AND NVL(refresh_date,report_date) = :new.report_date;
IF v_value = :new.value THEN
-- Not inserting any dupl.records
NULL
ELSIF :new.value= -999 THEN
--Update records in Daily_Report n/a value
UPDATE Daily_Report
SET value = -999,
date_updated = sysdate
WHERE rowid = v_rowid
ELSE
--Remove new records and records from Daily_Report table
--if value is different for the same report_date
INSERT INTO Estimates_Err_Report
SELECT action,
identifier,
master,
broker,
broker_analyst_code,
period_type,
period,
value,
rating_code,
ltg_rate,
currency_code,
dilution,
report_date,
refresh_date,
refresh_entered_date,
terminated_date,
terminated_entered_date,
date_appended,
curser,
date_updated,
sysdate,
media_flag
FROM Daily_Report
WHERE rowid = v_rowid;
INSERT INTO Estimates_Err_Report
Values(:new.action,
:new.identifier,
:new.master,
:new.broker,
:new.broker_analyst_code,
:new.period_type,
:new.period,
:new.value,
:new.rating_code,
:new.ltg_rate,
:new.currency_code,
:new.dilution,
:new.report_date,
:new.date_entered,
:new.referesh_date,
:new.refresh_entered_date,
:new.terminated_date,
:new.terminated_entered_date,
:new.date_appended,
:new.curuser,
sysdate,
null
:new.media_flag);
DELETE FROM Daily_Report
WHERE rowid = v_rowid;
END IF;
ExCEPTION
THEN no_dupl_found THEN
--Then checking if the records can be refreshed.This means if the sales
--value for current new record with the same set can be found
DECLARE no_such_value EXCEPTION;
PRAGMA EXCEPTION_INIT(no_such_value,100);
BEGIN
SELECT rowid,
NVL(refresh_date,report_date),
NVL(media_flag,0),
terminated_date
INTO v_rowid,
v_report_date,
v_media_flag,
v_terminated_date
FROM Daily_Report
WHERE identifier = :new.identifier
AND master =:new.master
AND broker =:new.broker
AND period_type =:new.period_type
AND period =:new.period
AND value =:new.value
AND NVL(refresh_date,report_date))
FROM Daily_Report
WHERE identifier =:new.identifier
AND master =:new.master
AND broker =:new.broker
AND period_type = :new.period_type
AND period = :new.period);
--IF NO_DATA_FOUND then see below Exception no_such_value
--Updating refresh_date and refresh_entered_date if sales value
--is same for this set
IF :new.report_date > v_report_date
AND v_terminated_date is null
AND :new.broker_analyst_code = :old.broker.analyst_code THEN
IF v_media_flag =0 and :new.media_flag!=0 THEN
v_media_flag:=:new.media_flag;
END IF
UPDATE Daily_Report
SET refresh_date =:new.report_date,
refresh_entered_date =:new.date_entered,
date_updated =:sysdate,
media_flag = v_media_flag
WHERE rowid = v_rowid;
ELSIF :new.report_date > v_report_date
AND v_terminated_date is not null
AND v_terminated_date > v_report_date
AND :new.broker_analyst_code =:old.broker_analyst_code THEN
UPDATE daily_report
SET refresh_date =:new.report_date
WHERE rowid = v_rowid;
ELSEIF :new.report_date > v_report_date
AND v_terminated_date is null
AND :new.broker_analyst_code <> :old.broker_analyst_code THEN
UPDATE daily_report
SET error_code =14
WHERE rowid = v_rowid;
INSERT INTO Daily_Report(action,
identifier,
broker,
master,
period,
period_type,
broker_analyst_code,
value,
rating_code,
ltg_rate,
currency_code,
dilution,
report_date,
date_entered,
refresh_date,
refresh_entered_date,
terminated_date,
terminated_entered_date,
date_appended,
curser,
date_updated,
error_code,
media_flag)
VALUES(:new.action,
:new.identifier,
:new.broker,
:new.master,
:new.period,
:new.period_type,
:new.broker_analyst_code,
:new.value,
:new.rating_code,
:new.ltg_rate,
:new.currency_code,
:new.dilution,
:new.report_date,
:new.date_entered,
:new.refresh_date,
:new.refresh_entered_date,
:new.terminated_date,
:new.terminated_entered_date,
:new.date_appended,
:new.curser,
sysdate,
15,
:new.media_flag);
ELSE
INSERT INTO Daily_Report(action,
identifier,
broker,
master,
period,
period_type,
broker_analyst_code,
value,
rating_code,
ltg_rate,
currency_code,
dilution,
report_date,
date_entered,
refresh_date,
refresh_entered_date,
terminated_date,
terminated_entered_date,
date_appended,
curser,
date_updated,
error_code,
media_flag)
VALUES(:new.action,
:new.identifier,
:new.broker,
:new.master,
:new.period,
:new.period_type,
:new.broker_analyst_code,
:new.value,
:new.rating_code,
:new.ltg_rate,
:new.currency_code,
:new.dilution,
:new.report_date,
:new.date_entered,
:new.refresh_date,
:new.refresh_entered_date,
:new.terminated_date,
:new.terminated_entered_date,
:new.date_appended,
:new.curser,
sysdate,
2,
:new.media_flag);
END IF; 

Open in new window

0
 
sdstuberCommented:
old and new refer to the row that the trigger is fired for.  Your trigger is on daily_estimates so old and new refer to the row in daily_estimates only.

Since an insert is creating that new row, there can't be an old

If you need an "old" view of some daily_reports row then you'll need a separate trigger on that table.
0
 
Pradeep_TiwariAuthor Commented:
for old view which type of trigger needed.
pls explain me in detail.
0
 
Pradeep_TiwariAuthor Commented:
give me answer as my question like:

(A) the trigger in question (AFTER_DAILY_ESTIMATES) is declared, initially, as a trigger "AFTER INSERT ON DAILY_ESTIMATES".
 
My understanding here is that this trigger is executed with respect only to one specific record in the table DAILY_ESTIMATES,
that has just been inserted in the table DAILY_ESTIMATES.  
 
(B) I think that for insert triggers, any reference using default table-field-name prefixes ":old" and ":new" would necessarily be references to the table that the trigger is based on (or would be based on, unless there were any explicit declarations, stating that ":old" and/or ":new" would be used to refer to some table _other than the table that the trigger is based on.    I do not see any declarations in this trigger that define ":old" or ":new" as being anything other than their default orientation, which would be to refer to the table that the trigger is based on.  And the trigger is based on DAILY_ESTIMATES.   So I think that both ":old" and ":new" refer to values in DAILY_ESTIMATES.  
 
(C)  It might be possible to suggest a counter argument:   "well, yes, the table is based on DAILY_ESTIMATES, but it inserts into DAILY_REPORT.".
 
Yes, this is true.  The trigger inserts into DAILY_REPORT.  It ALSO inserts into ESTIMATES_ERR_REPORT.  And for both of these tables, it inserts the value
":new.broker_analyst_code" into the table.   So I would think that if Oracle really thought that ":old" and ":new" were referring to anything other than DAILY_ESTIMATES, then there might be some confusion as to whether ":old" and ":new" were referring to ESTIMATES_ERR_REPORT, or to DAILY_REPORT.
 
(D)   The reason that this matters is because the variable (in the TRIGGER) ":old.broker_analyst_code" is SUPPOSED to be the broker_analyst_code
that is associated with a record in DAILY_REPORT that is returned as the result of the following query:
 
            SELECT rowid
                NVL (refresh_date, report_date)
                NVL (media_flag, 0)
                terminated_date
            INTO   v_rowid
                v_report_date,
                    v_media_flag
                    v_terminated_date
            FROM  Daily_Report
            WHERE  identifier = :new.identifier
            AND master = :new.master
            AND broker = :new.broker
            AND period_type = :new.period_type
            AND period = :new.period
            AND value = :new.value
            AND NVL (refresh_date, report_date) =
                (SELECT max(NVL(refresh_date, report_date))
                FROM Daily_Report
                WHERE identifier = :new.identifier
                AND  master = :new.master
                AND  broker =  :new.broker
                AND period_type  = :new.period_type
                AND period = :new.period)
 
OKAY.   Now, that query will generally produce a record; and the record it produces will be a record from DAILY_REPORT.
 
But what I do NOT see in this query is ANY reference to "broker_analyst_code" (whether "old" or "new").  
So I do NOT think that ORACLE is associating ":old.broker_analyst_code"
with the value of "broker_analyst_code" that is in whatever record would be produced by THIS query.
 
I think that the value of ":old.broker_analyst_code" is something else.   Not sure what.  It could be undefined,
or it could be the value of "broker_analyst_code" in the record that was just inserted into DAILY_ESTIMATES.
 
I agree that the record in DAILY_ESTIMATES is the major determinant of the query to DAILY_REPORT.
But--with respect to this query--the function of the record from DAILY_ESTIMATES
is to provide values that will be used as arguments for a query
that is used in order to get a record from DAILY_REPORT.
The record that is obtained by this query is a record from DAILY_REPORT.  
It is not a record from DAILY_ESTIMATES.
The value of the field "broker_analyst_code" from the record in DAILY_REPORT may, or may NOT, be the same
as the value of the field "broker_analyst_code" in the record from DAILY_ESTIMATES.
 
The point of the query is that we don't know that they are the same,
but we would like to check to see if they are the same.
But in order to check to see if they are the same,
we actually have to compare the value of the "broker_analyst_code" from the record from DAILY_REPORT,
with the value of "broker_analyst_code" from the record in DAILY_ESTIMATES.
And I don't think the logic in the trigger is doing that.
 
please let me know your thoughts on this
and if bug then how to correct it.



0
 
sdstuberCommented:
I don't know how else to explain it.

you can NOT refer to "old" inside an INSERT trigger.

there is no "old" record to refer to.


and a trigger is on a specific table.  old and new can only refer to the row for that table and no other.

A and B - yes.  

C- your counter argument can not be made.  That's simply not what a trigger is.  you declare a trigger "ON" something,  what you do inside the trigger is irrelevant to that definition.

D - you want to refer to some previous version of a row in a different table, that doesn't make sense.
The trigger is firing because you modified a row in daily_estimates,  therefore daily_reports hasn't changed.  So there is no "old" or "new", there is only what is right now.  So, you could, I guess call that "old", but you can't use  the :old keyword.  You'll have to select it.  If you have other triggers that might have fired that could have changed daily_reports prior to this trigger firing you'll have to capture the before and after images yourself.
0
 
Pradeep_TiwariAuthor Commented:
OK,
actually i have following table structure.
################################################################################
CREATE TABLE DAILY_ESTIMATES (ACTION NUMBER(1,0),
CONSTRAINT DAILY_ESTIMATES_PK PRIMARY KEY(ACTION),
IDENTIFIER VARCHAR2(5) NOT NULL,
MASTER VARCHAR2(4) NOT NULL,
BROKER CHAR(2) NOT NULL,
BROKER_ANALYST_CODE CHAR(5) DEFAULT 00001 NOT NULL,
PERIOD_TYPE CHAR(1) DEFAULT Y NOT NULL,
PERIOD CHAR(4) DEFAULT 0000 NOT NULL,
VALUE NUMBER NOT NULL,
RATING_CODE VARCHAR2(4),
LTG_RATE VARCHAR2(4),
CURRENCY_CODE VARCHAR2(3) DEFAULT USD,
DILUTION CHAR(1) DEFAULT N,
REPORTING_DATE DATE NOT NULL,
DATE_ENTERED DATE DEFAULT SYSDATE NOT NULL,
REFRESH_DATE DATE,
REFERESH_ENTERED_DATE DATE,
TERMINATED_DATE DATE,
TERMINATED_ENTERED_DATE DATE,
DATE_APPENDED DATE DEFAULT SYSDATE,
CURUSER VARCHAR2(30) DEFAULT USER,
MEDIA_FLAG NUMBER DEFAULT 0 )

###############################################################################
CREATE TABLE EARNINGS_DATA_NEW (MASTER VARCHAR2(4) NOT NULL,
CONSTRAINT EARNINGS_DATA_NEW_PK PRIMARY KEY(MASTER),
PERIOD DATE NOT NULL,
EARN_REPORT_DATE DATE NOT NULL,
ADJUST_NET_SALES NUMBER NOT NULL,
PERIOD_TYPE CHAR(1),
RESTATED_FLAG VARCHAR2(3),
NET_SALES NUMBER,
INCOME_BNRI NUMBER,
NON_RECURRING_ITEMS NUMBER,
NRI_REASON VARCHAR2(80),
INCOME_FROM_CONT_OPS NUMBER,
INCOME_FROM_DISC_OPS NUMBER,
INCOME_FROM_EXTRA_ITEMS NUMBER,
INCOME_FROM_ACCT_CHANGES NUMBER,
NET_INCOME NUMBER,
CREATOR VARCHAR2(10),
DATE_APPENDED DATE DEFAULT SYSDATE)

###############################################################################
CREATE TABLE ESTIMATES_ERR_REPORT(ACTION NUMBER(1,0),
IDENTIFIER VARCHAR2(5),
MASTER VARCHAR2(4),
BROKER CHAR(2),
BROKER_ANALYST_CODE CHAR(5),
PERIOD_TYPE CHAR(1),
PERIOD CHAR(4),
VALUE NUMBER,
RATING_CODE VARCHAR2(4),
LTG_RATE VARCHAR2(4),
CURRENCY_CODE VARCHAR2(3),
DILUTION CHAR(1),
REPORT_DATE DATE,
DATE_ENTERED DATE,
REFRESH_DATE DATE,
REFRESH_ENTERED_DATE DATE,
TERMINATED_DATE DATE,
TERMINATED_ENTERED_DATE DATE,
DATE_APPENDED DATE,
CURUSER VARCHAR2(30),
DATE_UPDATED DATE,
DATE_DELETED DATE DEFAULT SYSDATE,
MEDIA_FLAG NUMBER(1,0))

################################################################################
CREATE TABLE DAILY_REPORT(ACTION NUMBER(1,0),
IDENTIFIER VARCHAR2(5),
MASTER VARCHAR2(4),
BROKER CHAR(2),
BROKER_ANALYST_CODE CHAR(5),
PERIOD_TYPE CHAR(1),
PERIOD CHAR(4),
VALUE NUMBER,
RATING_CODE VARCHAR2(4),
LTG_RATE VARCHAR2(4),
CURRENCY_CODE VARCHAR2(3),
DILUTION CHAR(1),
REPORT_DATE DATE,
DATE_ENTERED DATE,
REFRESH_DATE DATE,
REFRESH_ENTERED_DATE DATE,
TERMINATED_DATE DATE,
TERMINATED_ENTERED_DATE DATE,
DATE_APPENDED DATE,
CURUSER VARCHAR2(30),
DATE_UPDATED DATE,
DATE_DELETED DATE DEFAULT SYSDATE,
MEDIA_FLAG NUMBER(1,0))
            
All i am created in my oracle database.
depend on that tables i am create one trigger "TRIGGER AFTER_DAILY_ESTIMATE"
when i am compile my trigger i am getting error like:
"Warning: execution completed with warning
TRIGGER AFTER_DAILY_ESTIMATES Compiled."

AND also getting compilation error when i am running with my oracle 10g OEM.
ERRORS ARE LIKE:

Line # = 33 Column # = 37 Error Text = PLS-00049: bad bind variable 'NEW.REPORT_DATE'
Line # = 37 Column # = 1 Error Text = PLS-00103: Encountered the symbol "ELSE" when expecting one of the following:

   ;
The symbol ";" was substituted for "ELSE" to continue.

Line # = 44 Column # = 1 Error Text = PLS-00103: Encountered the symbol "ELSE" when expecting one of the following:

   . ( * @ % & - + ; / at mod remainder rem return returning
   <an exponent (**)> and or || multiset
The symbol ";" was substituted for "ELSE" to continue.

Line # = 85 Column # = 1 Error Text = PLS-00049: bad bind variable 'NEW.REPORT_DATE'
Line # = 87 Column # = 1 Error Text = PLS-00049: bad bind variable 'NEW.REFERESH_DATE'
Line # = 88 Column # = 1 Error Text = PLS-00049: bad bind variable 'NEW.REFRESH_ENTERED_DATE'
Line # = 95 Column # = 1 Error Text = PLS-00103: Encountered the symbol "" when expecting one of the following:

   ) , * & | = - + < / > at in is mod remainder not rem => ..
   <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
   LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
The symbol "," was substituted for "" to continue.

Line # = 99 Column # = 1 Error Text = PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:

   begin case declare end exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe

please help me how to correct it and where the syntax is wrong and how to correct it.
trigger is given in Attach Code Snippet.
also give me corrected trigger if you made it right.
create or replace
TRIGGER AFTER_DAILY_ESTIMATES
AFTER INSERT ON DAILY_ESTIMATES
FOR EACH ROW
WHEN(new.value > 0 OR new.value = -999)
DECLARE
v_rowid  ROWID;
v_value  NUMBER;
v_report_date  DATE;
v_earned_period DATE;
v_err_number NUMBER;
v_err_text VARCHAR2(200);
v_media_flag NUMBER;
v_terminated_date DATe;
no_dupl_found EXCEPTION;
PRAGMA EXCEPTION_INIT (no_dupl_found,100);
BEGIN
IF :new.identifier ='SA' THEN
SELECT MAx(period)
INTO v_earned_period
FROM earnings_data_new
WHERE master = :new.master;
END IF;
IF(:new.identifier ='SA' AND v_earned_period < last_day(to_date(:new.period,'yymm')))
OR(:new.identifier='SA' AND v_earned_period IS NULL)
OR :new.identifier ='TP' THEN
BEGIN
SELECT rowid,
value
into v_rowid,
v_value
FROM Daily_Report
WHERE identifier = :new.identifier
AND master = :new.master
AND broker = :new.broker
ANd period = :new.period
AND period_type = :new.period_type
AND NVL(refresh_date,report_date) = :new.report_date;
IF v_value = :new.value THEN
-- Not inserting any dupl.records
NULL
ELSIF :new.value= -999 THEN
--Update records in Daily_Report n/a value
UPDATE Daily_Report
SET value = -999,
date_updated = sysdate
WHERE rowid = v_rowid
ELSE
--Remove new records and records from Daily_Report table
--if value is different for the same report_date
INSERT INTO Estimates_Err_Report
SELECT action,
identifier,
master,
broker,
broker_analyst_code,
period_type,
period,
value,
rating_code,
ltg_rate,
currency_code,
dilution,
report_date,
refresh_date,
refresh_entered_date,
terminated_date,
terminated_entered_date,
date_appended,
curser,
date_updated,
sysdate,
media_flag
FROM Daily_Report
WHERE rowid = v_rowid;
INSERT INTO Estimates_Err_Report
Values(:new.action,
:new.identifier,
:new.master,
:new.broker,
:new.broker_analyst_code,
:new.period_type,
:new.period,
:new.value,
:new.rating_code,
:new.ltg_rate,
:new.currency_code,
:new.dilution,
:new.report_date,
:new.date_entered,
:new.referesh_date,
:new.refresh_entered_date,
:new.terminated_date,
:new.terminated_entered_date,
:new.date_appended,
:new.curuser,
sysdate,
null
:new.media_flag);
DELETE FROM Daily_Report
WHERE rowid = v_rowid;
END IF;
ExCEPTION
THEN no_dupl_found THEN
--Then checking if the records can be refreshed.This means if the sales
--value for current new record with the same set can be found
DECLARE no_such_value EXCEPTION;
PRAGMA EXCEPTION_INIT(no_such_value,100);
BEGIN
SELECT rowid,
NVL(refresh_date,report_date),
NVL(media_flag,0),
terminated_date
INTO v_rowid,
v_report_date,
v_media_flag,
v_terminated_date
FROM Daily_Report
WHERE identifier = :new.identifier
AND master =:new.master
AND broker =:new.broker
AND period_type =:new.period_type
AND period =:new.period
AND value =:new.value
AND NVL(refresh_date,report_date))
FROM Daily_Report
WHERE identifier =:new.identifier
AND master =:new.master
AND broker =:new.broker
AND period_type = :new.period_type
AND period = :new.period);
--IF NO_DATA_FOUND then see below Exception no_such_value
--Updating refresh_date and refresh_entered_date if sales value
--is same for this set
IF :new.report_date > v_report_date
AND v_terminated_date is null
AND :new.broker_analyst_code = :old.broker.analyst_code THEN
IF v_media_flag =0 and :new.media_flag!=0 THEN
v_media_flag:=:new.media_flag;
END IF
UPDATE Daily_Report
SET refresh_date =:new.report_date,
refresh_entered_date =:new.date_entered,
date_updated =:sysdate,
media_flag = v_media_flag
WHERE rowid = v_rowid;
ELSIF :new.report_date > v_report_date
AND v_terminated_date is not null
AND v_terminated_date > v_report_date
AND :new.broker_analyst_code =:old.broker_analyst_code THEN
UPDATE daily_report
SET refresh_date =:new.report_date
WHERE rowid = v_rowid;
ELSEIF :new.report_date > v_report_date
AND v_terminated_date is null
AND :new.broker_analyst_code <> :old.broker_analyst_code THEN
UPDATE daily_report
SET error_code =14
WHERE rowid = v_rowid;
INSERT INTO Daily_Report(action,
identifier,
broker,
master,
period,
period_type,
broker_analyst_code,
value,
rating_code,
ltg_rate,
currency_code,
dilution,
report_date,
date_entered,
refresh_date,
refresh_entered_date,
terminated_date,
terminated_entered_date,
date_appended,
curser,
date_updated,
error_code,
media_flag)
VALUES(:new.action,
:new.identifier,
:new.broker,
:new.master,
:new.period,
:new.period_type,
:new.broker_analyst_code,
:new.value,
:new.rating_code,
:new.ltg_rate,
:new.currency_code,
:new.dilution,
:new.report_date,
:new.date_entered,
:new.refresh_date,
:new.refresh_entered_date,
:new.terminated_date,
:new.terminated_entered_date,
:new.date_appended,
:new.curser,
sysdate,
15,
:new.media_flag);
ELSE
INSERT INTO Daily_Report(action,
identifier,
broker,
master,
period,
period_type,
broker_analyst_code,
value,
rating_code,
ltg_rate,
currency_code,
dilution,
report_date,
date_entered,
refresh_date,
refresh_entered_date,
terminated_date,
terminated_entered_date,
date_appended,
curser,
date_updated,
error_code,
media_flag)
VALUES(:new.action,
:new.identifier,
:new.broker,
:new.master,
:new.period,
:new.period_type,
:new.broker_analyst_code,
:new.value,
:new.rating_code,
:new.ltg_rate,
:new.currency_code,
:new.dilution,
:new.report_date,
:new.date_entered,
:new.refresh_date,
:new.refresh_entered_date,
:new.terminated_date,
:new.terminated_entered_date,
:new.date_appended,
:new.curser,
sysdate,
2,
:new.media_flag);
END IF;

Open in new window

0
 
sdstuberCommented:
please post the real trigger code.

if that's it, the problem is it's not done.  It's missing semi-colons
it's missing conditions, your if and end if's don't match.
you declare two new exceptions but don't raise them.
It looks like you're trying to use them where you might have no_data_found exceptions
if so, then just use that.

One of the comments indicate no_data_found should see the "no_such_value" exception
but nothing in the code will raise such an exception and there is no exception block in that scope
to capture the exception even if you did raise one.


given all of the missing end-points to various blocks within the code it's hard to say what any one error might be from
and since some lines and blocks don't end, The parser can't read tell one line from another so even lines that are legal
may still be mis-parsed because they are combined with the previous line or block.

attached below is a GUESS on my part of how some of your if-endif blocks and begin/end blocks were intended
It's "possible" I've guessed correctly, but you will need to do a lot of work to confirm this code.
Given that the starting point was clearly unfinished, even if this is correct (I don't know if it is) it still may not be what you fully intended.


CREATE OR REPLACE TRIGGER after_daily_estimates
    AFTER INSERT
    ON daily_estimates
    FOR EACH ROW
    WHEN(new.VALUE > 0 OR new.VALUE = -999)
DECLARE
    v_rowid             ROWID;
    v_value             NUMBER;
    v_report_date       DATE;
    v_earned_period     DATE;
    v_err_number        NUMBER;
    v_err_text          VARCHAR2(200);
    v_media_flag        NUMBER;
    v_terminated_date   DATE;
BEGIN
    IF :new.identifier = 'SA'
    THEN
        SELECT   MAX(period)
          INTO   v_earned_period
          FROM   earnings_data_new
         WHERE   master = :new.master;
    END IF;
 
    IF (:new.identifier = 'SA' AND v_earned_period < LAST_DAY(TO_DATE(:new.period, 'yymm')))
    OR (:new.identifier = 'SA' AND v_earned_period IS NULL)
    OR :new.identifier = 'TP'
    THEN
        BEGIN
            SELECT   ROWID, VALUE
              INTO   v_rowid, v_value
              FROM   daily_report
             WHERE   identifier = :new.identifier
                 AND master = :new.master
                 AND broker = :new.broker
                 AND period = :new.period
                 AND period_type = :new.period_type
                 AND NVL(refresh_date, report_date) = :new.report_date;
        END;
    END IF;
 
    IF v_value = :new.VALUE
    THEN
        -- Not inserting any dupl.records
        NULL;
    ELSIF :new.VALUE = -999
    THEN
        --Update records in Daily_Report n/a value
        UPDATE   daily_report
           SET   VALUE = -999, date_updated = SYSDATE
         WHERE   ROWID = v_rowid;
    ELSE
        --Remove new records and records from Daily_Report table
        --if value is different for the same report_date
        INSERT INTO estimates_err_report
            SELECT   action,
                     identifier,
                     master,
                     broker,
                     broker_analyst_code,
                     period_type,
                     period,
                     VALUE,
                     rating_code,
                     ltg_rate,
                     currency_code,
                     dilution,
                     report_date,
                     refresh_date,
                     refresh_entered_date,
                     terminated_date,
                     terminated_entered_date,
                     date_appended,
                     curser,
                     date_updated,
                     SYSDATE,
                     media_flag
              FROM   daily_report
             WHERE   ROWID = v_rowid;
 
        INSERT INTO estimates_err_report
          VALUES   (
                        :new.action,
                        :new.identifier,
                        :new.master,
                        :new.broker,
                        :new.broker_analyst_code,
                        :new.period_type,
                        :new.period,
                        :new.VALUE,
                        :new.rating_code,
                        :new.ltg_rate,
                        :new.currency_code,
                        :new.dilution,
                        :new.report_date,
                        :new.date_entered,
                        :new.referesh_date,
                        :new.refresh_entered_date,
                        :new.terminated_date,
                        :new.terminated_entered_date,
                        :new.date_appended,
                        :new.curuser,
                        SYSDATE,
                        NULL,
                        :new.media_flag
                   );
 
        DELETE FROM   daily_report
              WHERE   ROWID = v_rowid;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        --Then checking if the records can be refreshed.This means if the sales
        --value for current new record with the same set can be found
 
        BEGIN
            SELECT   ROWID, NVL(refresh_date, report_date), NVL(media_flag, 0), terminated_date
              INTO   v_rowid, v_report_date, v_media_flag, v_terminated_date
              FROM   daily_report
             WHERE   identifier = :new.identifier
                 AND master = :new.master
                 AND broker = :new.broker
                 AND period_type = :new.period_type
                 AND period = :new.period;
            
            --Updating refresh_date and refresh_entered_date if sales value
            --is same for this set
            IF :new.report_date > v_report_date
           AND v_terminated_date IS NULL
           AND :new.broker_analyst_code = :old.broker.analyst_code
            THEN
                IF v_media_flag = 0 AND :new.media_flag != 0
                THEN
                    v_media_flag   := :new.media_flag;
                END IF;
 
                UPDATE   daily_report
                   SET   refresh_date           = :new.report_date,
                         refresh_entered_date   = :new.date_entered,
                         date_updated           = :sysdate,
                         media_flag             = v_media_flag
                 WHERE   ROWID = v_rowid;
            ELSIF :new.report_date > v_report_date AND v_terminated_date IS NOT NULL AND v_terminated_date > v_report_date AND :new.broker_analyst_code = :old.broker_analyst_code
            THEN
                UPDATE   daily_report
                   SET   refresh_date   = :new.report_date
                 WHERE   ROWID = v_rowid;
            ELSIF :new.report_date > v_report_date AND v_terminated_date IS NULL AND :new.broker_analyst_code <> :old.broker_analyst_code
            THEN
                UPDATE   daily_report
                   SET   ERROR_CODE   = 14
                 WHERE   ROWID = v_rowid;
 
                INSERT INTO daily_report(
                                             action,
                                             identifier,
                                             broker,
                                             master,
                                             period,
                                             period_type,
                                             broker_analyst_code,
                                             VALUE,
                                             rating_code,
                                             ltg_rate,
                                             currency_code,
                                             dilution,
                                             report_date,
                                             date_entered,
                                             refresh_date,
                                             refresh_entered_date,
                                             terminated_date,
                                             terminated_entered_date,
                                             date_appended,
                                             curser,
                                             date_updated,
                                             ERROR_CODE,
                                             media_flag
                           )
                  VALUES   (
                                :new.action,
                                :new.identifier,
                                :new.broker,
                                :new.master,
                                :new.period,
                                :new.period_type,
                                :new.broker_analyst_code,
                                :new.VALUE,
                                :new.rating_code,
                                :new.ltg_rate,
                                :new.currency_code,
                                :new.dilution,
                                :new.report_date,
                                :new.date_entered,
                                :new.refresh_date,
                                :new.refresh_entered_date,
                                :new.terminated_date,
                                :new.terminated_entered_date,
                                :new.date_appended,
                                :new.curser,
                                SYSDATE,
                                15,
                                :new.media_flag
                           );
            ELSE
                INSERT INTO daily_report(
                                             action,
                                             identifier,
                                             broker,
                                             master,
                                             period,
                                             period_type,
                                             broker_analyst_code,
                                             VALUE,
                                             rating_code,
                                             ltg_rate,
                                             currency_code,
                                             dilution,
                                             report_date,
                                             date_entered,
                                             refresh_date,
                                             refresh_entered_date,
                                             terminated_date,
                                             terminated_entered_date,
                                             date_appended,
                                             curser,
                                             date_updated,
                                             ERROR_CODE,
                                             media_flag
                           )
                  VALUES   (
                                :new.action,
                                :new.identifier,
                                :new.broker,
                                :new.master,
                                :new.period,
                                :new.period_type,
                                :new.broker_analyst_code,
                                :new.VALUE,
                                :new.rating_code,
                                :new.ltg_rate,
                                :new.currency_code,
                                :new.dilution,
                                :new.report_date,
                                :new.date_entered,
                                :new.refresh_date,
                                :new.refresh_entered_date,
                                :new.terminated_date,
                                :new.terminated_entered_date,
                                :new.date_appended,
                                :new.curser,
                                SYSDATE,
                                2,
                                :new.media_flag
                           );
            END IF;
        END;
END;

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now