Network Office reception Order found netb_order only the contents of a leapfrog because of complaints received, the user need to see the pre-acceptance list on No. 10000 return customer personnel on orders, as orders for different types according to the order form, associated with different list of orders, customer service response number 10000 there are different schedules in order, so consider using the trigger at the database level. This small amount of change
2. To achieve:
a. First of all, adding a field in netb_order replycontent called customer service number is used to keep the order of 10,000 responses
b. In the list of all the various orders with trigger, when a customer service number 10000, when added back, trigger the content will automatically sync back to the list of replycontent field netb_order
What is the trigger?
Is there a particular event when the automatic execution of code blocks. Similar to the stored procedure, but the user can not directly call them.
1, to allow / restrict changes to the table 2, automatically generate derived column, such as increasing the field from 3, 4 mandatory data consistency, to provide auditing and logging, 5, 6, to prevent invalid transactions, enabling complex business logic
Write an example of the first play, set up two tables test1, test2, table structure, like when to insert data into table test1 when using the trigger, automatically insert into test2 also a data table
create or replace trigger tr_insert_test1 before insert on test1 for each row begin insert into test2 values(:new.id,:new.name); end;
When using the insert into test1 (id, name) values ('123 ',' huxl '); test2 table when a record is also more
: New - latest column for a reference value;
: Old - a reference to the previous column value; these two variables only use the keyword "FOR EACH ROW" only exist. And the update statement, the two have, and insert only: new, delect only: old;
before that triggered the operation is complete, after that the trigger after the completion of
Following the completion of our business
3. Steps : a. See historical data will sync to netb_order table update netb_order o set o.replycontent= (select up.replycontent from netb_PackagesNewSetUp up where up.id=o.businessid); update netb_order o set o.replycontent= (select ps.reply from netb_phonenewsetup ps where ps.id=o.businessid); update netb_order o set o.replycontent= (select sr.reply from netb_selfhoodreq sr where sr.selfhoodreqid=o.businessid); update netb_order o set o.replycontent= (select ci.reply from netb_cards_info ci where ci.id=o.businessid); update netb_order o set o.replycontent= (select bo.reply from netb_basicorder bo where bo.id=o.businessid); 2. To order from the table and add a trigger drop trigger xxx Triggers the need for a single execution create or replace trigger tr_netb_PackagesNewSetUp after update of replycontent on netb_PackagesNewSetUp for each row begin update netb_order set replycontent=:new.replycontent where businessid=:old.id; end; create or replace trigger tr_netb_phonenewsetup after update of reply on netb_phonenewsetup for each row begin update netb_order set replycontent=:new.reply where businessid=:old.id; end; create or replace trigger tr_netb_selfhoodreq after update of reply on netb_selfhoodreq for each row begin update netb_order set replycontent=:new.reply where businessid=:old.selfhoodreqid; end; create or replace trigger tr_netb_cards_info after update of reply on netb_cards_info for each row begin update netb_order set replycontent=:new.reply where businessid=:old.id; end; create or replace trigger tr_netb_basicorder after update of reply on netb_basicorder for each row begin update netb_order set replycontent=:new.reply where businessid=:old.id; end;