Looking over TRIGGERS - UPDATING

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Looking over TRIGGERS - UPDATING

Author

Topic

dlmagers10
Starting Member

48 Posts

Posted - 2010-12-06 : 09:21:01

create TRIGGER update_rep
on CUSTOMER
after INSERT
as
declare @repnum char(5)

UPDATE rep
SET commission = commission + (rate * (SELECT balance FROM inserted))
WHERE rep_num = (SELECT rep_num FROM inserted);

Is what I have so far.

What I am trying to do is when I am updating a customer I need to be able to add the difference between the new balance and the old balance multipled by the sales rep's commission rate to the commission for the corresponding sales rep.

Would some please tell me what I am doing wrong? Or Right?

Thank you

Diana Magers

*There is no formula for success except perhaps an unconditional acceptance of life and what it brings.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-06 : 09:30:51

Can you only ever insert a single row?
If not then your trigger doesn't work because it will only get a single row value into the variable.
If so then you should error on multiple row updates just to mke sure.

UPDATE rep
SET commission = commission + rate * balance
WHERE rep_num in (SELECT rep_num FROM inserted);

that will cater for multiple row inserts as well.
The values in the table will be after the insert so you don't need to ge them from the inserted table.
It's a bit odd that you have a commission value already - shouldn't it be
UPDATE rep
SET commission = rate * balance
WHERE rep_num in (SELECT rep_num FROM inserted);

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-06 : 09:32:24

Where you are taking difference between New Balance and Old Balance ?

Vaibhav T

If I cant go back, I want to go fast...

dlmagers10
Starting Member

48 Posts

Posted - 2010-12-06 : 10:10:18

Actually, I think it will work with the

create TRIGGER add_customer
on CUSTOMER
after INSERT
as
declare @repnum char(5)

UPDATE rep
SET commission = commission + rate * (SELECT balance FROM inserted)
WHERE rep_num in (SELECT rep_num FROM inserted);

Diana Magers

*There is no formula for success except perhaps an unconditional acceptance of life and what it brings.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-06 : 10:37:13

declare @repnum char(5)
isn't used
and the update statement is that same as

UPDATE rep
SET commission = commission + rate * balance
WHERE rep_num in (SELECT rep_num FROM inserted);

for a single row.
For multiple row inserts yours will fail.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources