7 replies to this topic

[Amy]

Has anybody tried to used a TRIGGER that executes a STORED PROCEDURE which in turn executes CURSORS before ?

I'm developing a desktop application using C# , and i connect to the DB ,
and whenever the procedure is to run u recieve this error :
"Insert Error:Column name or number supplied values doesn't match table definition .
Invalid column name 'Department_ID'
Invalid column name 'Department_Name' "

---
ALTHOUGH i can execute the very same stored procedure through SQL Server , and it runs properly !!

[WingedPanther]

You will have to specify the database type you want it to work in. MySQL, SQL Server, and Oracle are radically different when dealing with triggers and stored procedures.

[Amy]

SQL Server is what I'm working in

[WingedPanther]

I'm pretty sure we've done some stuff like that in my company. I'd have to review it, however. If you posted what you're trying to do I may be able to look some stuff up or do some testing.

[Amy]

What I am trying to do :
* I have some equipments that need periodical maintenance; and maintenance can be one of many tasks that can be applied to that machine .
* This maintenance due according to two methods
-Hourly_Basis Maintenance : that should be applied regarding counters
-Monthly_Basis Maintenance : that should be applied periodically regarding Last_Maintenance_Data .
-Mixed_Basis Maintenance : that uses both basis - hourly and monthly basis -

What I have done :
* I had a stored procedure - using a cursor - to check all possible counters for each possible maintenance_task that can be performed on one single machine ;
* another similar stored procedure - using cursor as well - for monthly basis maintenance ;
* a stored procedure for mixed_Basis Maintenance : this one just calls the former stored procedures
* and finally , a stored procedure - named Maintenance_Planning - with a cursor that calls the above three procedures for each machine I have in the DB ..

then , I had a trigger to call Maintenance_Planning procedure automatically whenever needed .

but that doesn't work properly ..
what happens exactly is : I receive error when I try to run Maintenance_Planning procedure ;

YET , when I run Hourly_Basis procedure on a certain machine , and then run Monthly_Basis procedure and THEN run Maintenance_Planning procedure it works !!

----
Do you think I should not compartmentalize functions and put all code in a single stored procedure and try that ?
i don't really like that idea, but I will do it as a last solution :/

[WingedPanther]

That may be the way to go. I'm no expert on stored procedures.

[Amy]

Thanks for your concern anyway !

[Stu_328]

Whats your error?