Home » SQL & PL/SQL » SQL & PL/SQL » Trigger works But !!!!!!!!!!!!
Trigger works But !!!!!!!!!!!! [message #18803] |
Thu, 14 February 2002 11:44 |
Rm69
Messages: 39 Registered: January 2002
|
Member |
|
|
My trigger works but the only problem is that it writes on one line and then skips a line then writes again. ie it writes a recoed on line 1 the next one on line 3 the next one on line 5 etc. How can l fix that?
CREATE OR REPLACE TRIGGER logmischanges1
BEFORE INSERT OR DELETE OR UPDATE
ON mis_wrkflw_capital
FOR EACH ROW
DECLARE
v_changetype CHAR (1);
v_num NUMBER (3);
v_branch NUMBER (10);
v_sbrokercd VARCHAR2 (10);
v_received NUMBER (15, 2);
v_rec_count NUMBER (10);
v_prequota NUMBER (15, 2);
v_prequota_count NUMBER (10);
v_batched NUMBER (15, 2);
v_batched_count NUMBER (10);
v_scanned_received NUMBER (15, 2);
v_scanrec_count NUMBER (10);
v_captured NUMBER (15, 2);
v_cpa_count NUMBER (10);
v_checked NUMBER (15, 2);
v_chkd_count NUMBER (10);
v_rules NUMBER (15, 2);
v_rules_count NUMBER (10);
v_returned NUMBER (15, 2);
v_ret_count NUMBER (10);
v_disburse NUMBER (15, 2);
v_dis_count NUMBER (10);
v_active NUMBER (15, 2);
v_active_count NUMBER (10);
v_pre_reject NUMBER (15, 2);
v_prerej_count NUMBER (10);
v_rejected NUMBER (15, 2);
v_rej_count NUMBER (10);
v_summary_date DATE;
v_change_date DATE;
v_change_type VARCHAR2 (1);
v_changed_by VARCHAR2 (15);
v_timestamp DATE;
BEGIN
/* 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */
IF INSERTING
THEN
v_changetype := 'I';
ELSIF UPDATING
THEN
v_changetype := 'U';
ELSE
v_changetype := 'D';
END IF;
/* Record all the changes made to mis_wrkflw_Capital in
mis_wrkflw_CapitalAudit. Use SYSDATE to generate the timestamp, and
USER to return the userid of the current user. */
-- If v_changetype = 'I' or v_ChangeType = 'U' or v_ChangeType = 'D'
-- Then
INSERT INTO mis_wrkflw_capitalaudit
(change_type, changed_by, TIMESTAMP, num, branch,
sbrokercd, received, rec_count, prequota,
prequota_count, batched, batched_count,
scanned_received, scanrec_count, captured,
cpa_count, checked, chkd_count, rules,
rules_count, returned, ret_count,
disburse, dis_count, active, active_count,
pre_reject, prerej_count, rejected,
rej_count, summary_date, change_date)
--values ( :new.col1, :new.col2
VALUES (v_change_type, USER, SYSDATE, :OLD.num, :OLD.branch,
:OLD.sbrokercd, :OLD.received, :OLD.rec_count, :OLD.prequota,
:OLD.prequota_count, :OLD.batched, :OLD.batched_count,
:OLD.scanned_received, :OLD.scanrec_count, :OLD.captured,
:OLD.cpa_count, :OLD.checked, :OLD.chkd_count, :OLD.rules,
:OLD.rules_count, :OLD.returned, :OLD.ret_count,
:OLD.disburse, :OLD.dis_count, :OLD.active, :OLD.active_count,
:OLD.pre_reject, :OLD.prerej_count, :OLD.rejected,
:OLD.rej_count, :OLD.summary_date, :OLD.change_date);
--End if;
END logmischanges1;
|
|
|
Re: Trigger works But !!!!!!!!!!!! [message #18827 is a reply to message #18803] |
Sun, 17 February 2002 17:09 |
seng
Messages: 191 Registered: February 2002
|
Senior Member |
|
|
Hi, i don't see any problem in your source code. Ay insert,delete and update should follow with inserting an record by this trigger .
However, my suggestion is to try using AFTER
AFTER INSERT OR DELETE OR UPDATE
Hopefully, this will help you
|
|
|
Goto Forum:
Current Time: Sat Sep 28 21:02:28 CDT 2024
|