Problem creating database trigger from vb.net - Help!
-
Hello everybody, I've cretaed a database application that build tables/triggers/stored-procedures at runtime (Oracle DB 10g). After all tables created, the application starts creating the triggers and the SPs. this is small code: -------------------------------------------------------------------------------- Public Shared Sub CreateTriggers() Dim Err As String = "" Dim HasErr As Boolean = False Dim CmdTmp As OleDbCommand = New OleDbCommand With CmdTmp .Connection = oConn 'creating trigger(s) .CommandText = "CREATE TRIGGER change_status AFTER INSERT ON invoices " & _ "REFERENCING NEW AS newRow FOR EACH ROW " & _ "BEGIN update orders set orders.order_status='Close' where orders.orderID = :newRow.orderID; " & _ "END change_status;" .CommandType = CommandType.Text Try .ExecuteNonQuery() Catch ex As Exception HasErr = True Err += ex.Message + Chr(13) End Try 'creating stored-procedure(s) .CommandText = "CREATE OR REPLACE PROCEDURE Add_Inventory(SID varchar2, IID varchar2, Amount Number ) " & _ "IS " & _ "BEGIN " & _ "Insert Into Inventory(StoreID, ItemID, Inv_Amount, Inv_DateM, Inv_DateY) " & _ "Values(SID, IID, Amount, To_Number(To_Char(SysDate, 'MM')), To_Number(To_Char(SysDate, 'YYYY'))); " & _ "END;" Try .ExecuteNonQuery() Catch ex As Exception HasErr = True Err += ex.Message + Chr(13) End Try ... ... -------------------------------------------------------------------------------- now, I dont get exceptions when the app runs, but the triggers\SPs were NOT created.. I also created tables at run-time and there is no problem..the tables have been created... There is no problem creating the triggers\SPs from sqlplus with the same user so I dont think that is a permissions problem (when I copy the trigger/SP code & paste it in sqlplus, I get the message(s) Trigger created or procedure created ) I also CAN delete trigger(s)\SP(s) from the application but CANNOT create them.. what could be the problem? please help me out here..I'm stuck... Thank You RoyRose
-
Hello everybody, I've cretaed a database application that build tables/triggers/stored-procedures at runtime (Oracle DB 10g). After all tables created, the application starts creating the triggers and the SPs. this is small code: -------------------------------------------------------------------------------- Public Shared Sub CreateTriggers() Dim Err As String = "" Dim HasErr As Boolean = False Dim CmdTmp As OleDbCommand = New OleDbCommand With CmdTmp .Connection = oConn 'creating trigger(s) .CommandText = "CREATE TRIGGER change_status AFTER INSERT ON invoices " & _ "REFERENCING NEW AS newRow FOR EACH ROW " & _ "BEGIN update orders set orders.order_status='Close' where orders.orderID = :newRow.orderID; " & _ "END change_status;" .CommandType = CommandType.Text Try .ExecuteNonQuery() Catch ex As Exception HasErr = True Err += ex.Message + Chr(13) End Try 'creating stored-procedure(s) .CommandText = "CREATE OR REPLACE PROCEDURE Add_Inventory(SID varchar2, IID varchar2, Amount Number ) " & _ "IS " & _ "BEGIN " & _ "Insert Into Inventory(StoreID, ItemID, Inv_Amount, Inv_DateM, Inv_DateY) " & _ "Values(SID, IID, Amount, To_Number(To_Char(SysDate, 'MM')), To_Number(To_Char(SysDate, 'YYYY'))); " & _ "END;" Try .ExecuteNonQuery() Catch ex As Exception HasErr = True Err += ex.Message + Chr(13) End Try ... ... -------------------------------------------------------------------------------- now, I dont get exceptions when the app runs, but the triggers\SPs were NOT created.. I also created tables at run-time and there is no problem..the tables have been created... There is no problem creating the triggers\SPs from sqlplus with the same user so I dont think that is a permissions problem (when I copy the trigger/SP code & paste it in sqlplus, I get the message(s) Trigger created or procedure created ) I also CAN delete trigger(s)\SP(s) from the application but CANNOT create them.. what could be the problem? please help me out here..I'm stuck... Thank You RoyRose
he y don't u try stored procedures instend of command text
-
he y don't u try stored procedures instend of command text