Re: Information wanted on stored procedures and triggers

krienke@infko.uni-koblenz.de (Rainer Krienke) writes: >Hi, >Is there anyone in the net who could give me a short and easy to >understand example of how (and when) to use triggers and stored procedure in an >Informix environment. >Perhaps some sqlcode that demonstrates the use >of these features. A stored procedure is a sequence of SQL code, perhaps with imbedded logic statements that enables one or more SQL statements to be executed as a set. It greatly reduces traffic between client and server processes, and allows database-layer code to be isolated in one place. It also enables you to secure tables from general access and restrict database processing to the procedure code. A trigger is one or more SQL statements that executes upon the occurence of an insert, update, or delete event. They are generally used to enforce business rules. An (abbreviated) example of a stored procedure: CREATE PROCEDURE ins_overpymt(p_userid_srl INTEGER, p_journal_num INTEGER, p_trans_num SMALLINT, p_acct_num INTEGER, p_overpymt_amt DECIMAL(11,2)) RETURNING INTEGER; DEFINE p_status INTEGER; -- account columns DEFINE a_acct_type LIKE account.acct_type; DEFINE a_int_case_num LIKE account.int_case_num; DEFINE a_party_num LIKE account.party_num; DEFINE a_party_code LIKE account.party_code; . . . BEGIN LET p_check_stub_descr = NULL; SELECT account.acct_type, account.int_case_num, account.party_num, account.party_code, . . INTO a_acct_type, a_int_case_num, a_party_num, a_party_code, . . FROM account, OUTER acct_trust WHERE account.acct_num = p_acct_num AND acct_trust.acct_num = account.acct_num SELECT profile.overpay_ret_limit INTO poverpay_ret_limit FROM case, profile WHERE case.int_case_num = a_int_case_num AND profile.locn_code = case.locn_code AND profile.court_type = case.court_type; IF poverpay_ret_limit IS NULL OR p_overpymt_amt < poverpay_ret_limit THEN EXECUTE PROCEDURE ins_acct_dist(p_userid_srl, p_acct_num, "MC", 0, 0, 0) INTO p_status; IF p_status <> 0 THEN RETURN p_status; END IF; ELSE INSERT INTO account(acct_num, acct_type, int_case_num, party_num, party_code, timepay_num, timepay_seq, orig_amt_due, amt_due, amt_paid, amt_credit, status, due_date, userid_srl) VALUES (0, "T", a_int_case_num, a_party_num, a_party_code, NULL, NULL, p_overpymt_amt, NULL, NULL, NULL, NULL, NULL, p_userid_srl); END IF RETURN p_status; END END PROCEDURE; An example of a trigger: CREATE TRIGGER u_accountUPDATE OF timepay_num, timepay_seq ON account REFERENCING OLD AS old NEW AS new FOR EACH ROW WHEN (old.timepay_num != new.timepay_num) (EXECUTE PROCEDURE ins_ch_account (new.int_case_num, new.acct_num, new.timepay_num, old.timepay_num)); ___ ___ Senior Consultant / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111 dberg@informix.com Opinions expressed herein are my own.