SEE ATTACHMENT
acip.vcf (298 Bytes)
Oracle cursors handle before and after snapshot with .before or .after snapshot
of the the state of the requested cursor ..more info is available at
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/06_ora.htm#36656
//if you want a sql statement to be executed before a dml event use BEFORE DML
as in
CREATE OR REPLACE TRIGGER bb_deptchg_trg
BEFORE UPDATE OF idDepartment ON bb_department
FOR EACH ROW
BEGIN
UPDATE BB_PRODUCT
SET idDepartment = :NEW.idDepartment
WHERE idDepartment = :OLD.idDepartment;
END;
/
//conversely if you wish to execute a DML operation which will occur after a DML
event use the AFTER DML
CREATE or REPLACE Trigger product_inventory_trg
AFTER UPDATE OF orderplaced on proxy_user.BB_BASKET
FOR EACH ROW
WHEN (OLD.orderplaced <> 1 AND NEW.orderplaced = 1) --OLD is previous record
read ..NEW is New record read
DECLARE
CURSOR basketitem_cur IS
SELECT idproduct,quantity,option1 FROM proxy_user.BB_BASKETITEM
WHERE idbasket = NEW.idbasket;
lv_chg_num NUMBER(3,1);
END product_inventory_trig;
/
hth
Martin Gainty
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.
Morning Aci,
Is there a way to see OLD.xx and NEW.xx values of the table
in trigger debug session??
There is, if you don't mind a little extra coding. Oracle's debugger is
the cause of the problem I'm afraid and Toad hooks into it. If Oracle
can't tell Toad the value, Toad can't display it for you.
You need to declare a couple of variable and in your code, do:
...
declare
vOldSomething tablename.columnname%type;
vNewSomething tablename.columnname%type;
...
begin
...
vOldSomething := :old.something;
vNewSomething := :new.something;
...
end;
And so on. Now you should be able to hover or watch these temporary
variables. When done debugging, comment out the various bits to save a
few microseconds of CPU when the system goes live.
HTH
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk