HAWOK XML Faq

Using XMLType In Triggers

• How To Make Money Writing Easy Web Articles

You can use the new and old binds inside triggers to read and modify the XMLType column values. For INSERT and UPDATE statements, you can modify the new value to change the value being inserted.

Example 4-39 Creating XMLType Triggers

For example, you can write a trigger to change the purchase order if it does not contain a shipping address:

CREATE OR REPLACE TRIGGER po_trigger

BEFORE INSERT OR UPDATE ON po_xml_tab FOR EACH ROW declare pono Number; begin if inserting then:

if :NEW.poDoc.existsnode('//SHIPADDR') = 0 then

:NEW.poDoc := xmltype('<PO>INVALID_PO</PO>'); end if; end if;

when updating, if the old poDoc has purchase order number different from the new one then make it an invalid PO.

if updating then:

if :OLD.poDoc.extract('//PONO/text()').getNumberVal() ! =

:NEW.poDoc.extract('//PONO/text()').getNumberVal() then

:NEW.poDoc := xmltype('<PO>INVALID_PO</PO>'); end if; end if;

end;

This example is only an illustration. You can use the XMLType value to perform useful operations inside the trigger, such as validation of business logic or rules that the XML document should conform to, auditing, and so on.

0 0