OraFAQ Forum: SQL & PL/SQL » Trigger Compiles But does not capture all Details

Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Compiles But does not capture all Details

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Trigger Compiles But does not capture all Details [message #18747]

Wed, 13 February 2002 03:26

Rm69
Messages: 39
Registered: January 2002

Member

l've written a trigger to log changes in the Mis_wrkflw_Capital table. So if changes are made l want to log them into my audit table.It compiles with no errors but does not insert the changes.It captures the change_type, changed_by, timestamp details and everything else is null.Where am l going wrong?

CREATE OR REPLACE TRIGGER LogMisChanges
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
/* Use '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
RS_audit. 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
(V_Changetype, User, Sysdate,
V_Num,
V_Branch,
V_Sbrokercd,
V_Received,
V_Rec_Count,
V_Prequota,
V_Prequota_Count,
V_Batched,
V_Batched_Count,
V_Scanned_Received,
V_Scanrec_Count,
V_Captured,
V_Cpa_Count,
V_Checked,
V_Chkd_Count,
V_Rules,
V_Rules_Count,
V_Returned,
V_Ret_Count,
V_Disburse,
V_Dis_Count,
V_Active,
V_Active_Count,
V_Pre_Reject,
V_Prerej_Count,
V_Rejected,
V_Rej_Count,
V_Summary_Date,
V_Change_Date
);
END LogMisChanges;
end if;

Report message to a moderator

Re: Trigger Compiles But does not capture all Details [message #18768 is a reply to message #18747]

Wed, 13 February 2002 17:18

Raj
Messages: 411
Registered: November 1998

Senior Member

one error i found was
you have given assign sign(:=) in the if condition.Also try to use old and new for values.

Report message to a moderator

Previous Topic:	sql statement
Next Topic:	Scrollable Resultset

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 02:48:16 CDT 2024