Hey guys...I have this question which I was unable to solve.

Lets assume we Have 2 tables:

                Account (AccountNum(PK), AccountName, Debit, Credit, Balance(diff between Debit and Credit))                 TransactionHistory(TransactioID(PK), AccountNum(FK),TransAmount, TransType(Debit or Credit), TimeofTrans)

Now, the task is to design a SP along with trigger that will depict the banking transaction (SP will take 3 parameters: Account No, Transaction Type and Amount.

We have to make sure to check the balance in the Account before proceeding in case of Debit. When a transaction is done fire a trigger that put corresponding records in transaction history table both in case of Credit and Debit. Also we have to ensure that SP will run in any situation and will throw exception that is understood by Non-Technical Users.).

So what exactly are you asking for?

Hi Andy,

Based on the assumption of two tables above:-

The task is to design a SP along with trigger that will depict the banking transaction (SP will take 3 parameters: Account No, Transaction Type and Amount.

We have to make sure to check the balance in the Account before proceeding in case of Debit. When a transaction is done fire a trigger that put corresponding records in transaction history table both in case of Credit and Debit. Also we have to ensure that SP will run in any situation and will throw exception that is understood by Non-Technical Users.).

As Andy said...what are you asking for?

But, the trigger should be on the account history table.. firing when a record is inserted into it to update the balance on account.. you have it backwards.

Also, we are not a substitute for either of the following:

a) paid consultants 

b) you doing your own home/course work.

Now, the task is to design a SP along with trigger

No, this is wrong.

A trigger is something you write to uphold database integrity, either by
checking validity or performing cascading updates.

Stored procedures are good, but what is some bypasses the stored procedure
and perform updates directly?

My approach when I do database design is that I design the table with
constraints and triggers to protect the data from bad application code, and
stored procedures is application from my perspective. (Particularly when
some other developer writes it!)

But we do have tables where a stored procedure makes the major work, and
where I assume that no one would actually bypass that procedure, simply
because the SP updates so many tables. And, as it happens, that is a
procdure which performs a task similar to yours. That is, add a transaction
and updates cash holdings for an account.

Put everything in the procedure. Don't split logic between the SP and the
trigger. That is a brittle design.