Insert trigger

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hello sir
I have two table account1 and account i want to make insert trigger on account i tried ii but not success please tell me

out put : when i enter account value should be change in balance

SQL

create table account1
(
accountno int primary key ,
cbalance int ,
)

create table account
(
accountno int foreign key references account1,
transtype nchar(10),
Ammount int

)

create trigger trgdemo
on account
for insert

as begin
DECLARE @TransType NCHAR(10)
DECLARE @Accountno INT
Declare @cbalance INT

SELECT @TransType =transtype FROM INSERTED
SELECT @Accountno =Accountno FROM INSERTED
SELECT @cbalance = cbalance FROM inserted

IF(@TransType= 'w')
  BEGIN
    Set @cbalance = ammount - cbalance
  END
ELSE IF (@TransType= 'd')
  BEGIN
    Set @cbalance = ammount + cbalance
  END
END

pls tell me where i m goin wrong :doh:

Posted 26-Oct-10 1:17am

Amit chirutkar

Updated 26-Oct-10 1:21am

DaveAuld

v2

Add a Solution

Comments

DaveAuld 26-Oct-10 7:19am

You should really format your code using pre tags.......

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rajesh Anuhya · Answer 1 · 2010-10-26T21:27:00

There are Errors in your Triggers

your code may be link this...

create trigger trgdemo
on account
for insert

as begin
DECLARE @TransType NCHAR(10)
DECLARE @Accountno INT
Declare @cbalance INT

SELECT @TransType =transtype FROM INSERTED
SELECT @Accountno =Accountno FROM INSERTED
SELECT @Amount = Amount FROM inserted

IF(@TransType= 'w')
  BEGIN
update accounts1 Set cbalance = cbalance - @Amount where accountno =@Accountno;
  END
ELSE IF (@TransType= 'd')
  BEGIN
update accounts1 Set cbalance = cbalance + @Amount where accountno =@Accountno;    
  END


END

there is No cbalance field in your account table

SELECT @cbalance = cbalance FROM inserted

Rajesh Anuhya · Answer 2 · 2010-10-26T23:07:00

Solution 5

Yes Amit.., But there is no Cbalance column in the account table,

how can u write the below statement ????

SELECT @cbalance = cbalance FROM inserted

use the above code i have given .., try that and let me know if it's working or not...

Posted 26-Oct-10 23:07pm

Rajesh Anuhya

Rajesh Anuhya · Answer 3 · 2010-10-26T01:27:00

where is your update command in your trigger body???

create trigger trgdemo
on account
for insert

as begin
DECLARE @TransType NCHAR(10)
DECLARE @Accountno INT
Declare @cbalance INT

SELECT @TransType =transtype FROM INSERTED
SELECT @Accountno =Accountno FROM INSERTED
SELECT @cbalance = cbalance FROM inserted

IF(@TransType= 'w')
  BEGIN
    Set @cbalance = ammount - cbalance
  END
ELSE IF (@TransType= 'd')
  BEGIN
    Set @cbalance = ammount + cbalance
  END

update accounts1 set cbalance =@cbalance where accountno =@@Accountno;

END

See the above code.., this may help you...

Amit chirutkar · Answer 4 · 2010-10-26T21:16:00

Sir i m using trigger like this but not working pls suggest me

create table account1
(
accountno int primary key ,
cbalance int ,
)

create table account
(
accountno int foreign key references account1,
transtype nchar(10),
Ammount int

)


create trigger trgdemo
on account
for insert

as begin
DECLARE @TransType NCHAR(10)
DECLARE @Accountno INT
Declare @cbalance INT

SELECT @TransType =transtype FROM INSERTED
SELECT @Accountno =Accountno FROM INSERTED
SELECT @cbalance = cbalance FROM inserted

IF(@TransType= 'w')
  BEGIN
update accounts1 Set @cbalance = ammount - cbalance where accountno =@Accountno;
  END
ELSE IF (@TransType= 'd')
  BEGIN
update accounts1 Set @cbalance = ammount + cbalance where accountno =@Accountno;    
  END


END

Amit chirutkar · Answer 5 · 2010-10-26T23:03:00

Solution 4

No i have to change in my first table account1 [ cbalance ]
coloumn

Posted 26-Oct-10 23:03pm

Amit chirutkar

Insert trigger

5 solutions

Solution 3

Solution 5

Solution 1

Solution 2

Solution 4

Add your solution here

Preview 0