asked on

Sql Triggers to manage Bills ( SQLExpress )

Hi all,

I need your help to write some triggers.. that will manage 3 tables:

treatment                  bill             customer
--------             ------        --------
price                    total             balance
billID                    customerID        ....
...                    ....

each customer has many bills and each bill can hold many treatments
i want to make triggers so that.. after inserting/updating/deleting
each treatment.. the bill-total will be updated .. and then the customer balance will be updated as well.

now the the bill record is created first with total =0 , then treatments are added to this bill.

thanks

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Misbah

ASKER

thanks alot for ur super quick response :)

plz .. can u point me to the place where I can create these triggers ... I am using
Microsoft SQL Server Management Studio Express ... and under my database
there is a folder called Database Triggers .. but when I right click on it .. the menu has
no 'new' or 'add' .. 'just refresh' ... !!!

Guy Hengel [angelIII / a3]

This is not a database trigger (those are for coding when a database-wide trigger is called).

what I posted is a table-level trigger. Open a SQL Script window, paste the code, and run the code.
Assuming I didn't write any syntax errors, the triggers are created.
when you right-click then on the tables, you should find some context menu speaking about the triggers...

Misbah

ASKER

ok .. thanks.. ur answer is happily accepted :)

I hope I will need just a little to let it works :)

I run them but i got these error messages :
--------------------------------------------------
Msg 4104, Level 16, State 1, Procedure trg_treatment_new, Line 5
The multi-part identifier "t.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_new, Line 5
The multi-part identifier "i.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_new, Line 5
The multi-part identifier "t.price" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_new, Line 10
The multi-part identifier "t.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_new, Line 10
The multi-part identifier "i.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_new, Line 10
The multi-part identifier "t.price" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_delete, Line 6
The multi-part identifier "t.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_delete, Line 6
The multi-part identifier "d.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_delete, Line 6
The multi-part identifier "t.price" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_delete, Line 11
The multi-part identifier "t.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_delete, Line 11
The multi-part identifier "d.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_delete, Line 11
The multi-part identifier "t.price" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_update, Line 6
The multi-part identifier "t.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_update, Line 6
The multi-part identifier "d.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_update, Line 6
The multi-part identifier "t.price" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_update, Line 6
The multi-part identifier "t.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_update, Line 6
The multi-part identifier "i.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_update, Line 6
The multi-part identifier "t.price" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_update, Line 14
The multi-part identifier "t.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_update, Line 14
The multi-part identifier "d.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_update, Line 14
The multi-part identifier "t.price" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_update, Line 14
The multi-part identifier "t.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_update, Line 14
The multi-part identifier "i.billid" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_treatment_update, Line 14
The multi-part identifier "t.price" could not be bound.

Guy Hengel [angelIII / a3]

Well, that is because I only copy/pasted some code around :-(
here a reviesed version:

create trigger trg_treatment_new
on treatment
for insert
as
update bill
set total = total + (select sum(i.price) from inserted i where b.billid = i.billid )
from bill b
where b.billid in ( select billid from inserted )

update customer
set balance = balance + ( select sum(i.price) from inserted i where c.billid = i.billid )
from customer c
join bill b
on b.customerid = c.customerid
and b.billid in ( select billid from inserted )

GO

create trigger trg_treatment_delete
on treatment
for delete
as
update bill
set total = total - (select sum(d.price) from deleted d where b.billid = d.billid )
from bill b
where b.billid in ( select billid from deleted )

update customer
set balance = balance - ( select sum(d.price) from deleted d where c.billid = d.billid )
from customer c
join bill b
on b.customerid = c.customerid
where b.billid in ( select billid from deleted )

GO

create trigger trg_treatment_update
on treatment
for update
as
update bill
set total = total
- (select sum(d.price) from deleted d where b.billid = d.billid )
+ (select sum(i.price) from inserted i where b.billid = i.billid )
from bill b
where b.billid in ( select billid from deleted )

update customer
set balance = balance
- ( select sum(d.price) from deleted d where c.billid = d.billid )
+ (select sum(i.price) from inserted i where c.billid = i.billid )
from customer c
join bill b
on b.customerid = c.customerid
where b.billid in ( select billid from deleted )

GO

Misbah

ASKER

ok.. thanks again angelIII :)

but i still get some errors !!
--------------
Msg 207, Level 16, State 1, Procedure trg_treatment_new, Line 11
Invalid column name 'billid'.
Msg 207, Level 16, State 1, Procedure trg_treatment_delete, Line 12
Invalid column name 'billid'.
Msg 207, Level 16, State 1, Procedure trg_treatment_update, Line 15
Invalid column name 'billid'.
Msg 207, Level 16, State 1, Procedure trg_treatment_update, Line 16
Invalid column name 'billid'.
------------------
!!!!!!
strange.. there is billid column !!

did u see my second part question here : ?

https://www.experts-exchange.com/questions/21871596/Triggers-for-Bills-Management-2.html

Guy Hengel [angelIII / a3]

This will be better (as there is no billid column in the table customer)

create trigger trg_treatment_new
on treatment
for insert
as
update bill
set total = total + (select sum(i.price) from inserted i where b.billid = i.billid )
from bill b
where b.billid in ( select billid from inserted )

update customer
set balance = balance + ( select sum(i.price) from inserted i where b.billid = i.billid )
from customer c
join bill b
on b.customerid = c.customerid
and b.billid in ( select billid from inserted )

GO

create trigger trg_treatment_delete
on treatment
for delete
as
update bill
set total = total - (select sum(d.price) from deleted d where b.billid = d.billid )
from bill b
where b.billid in ( select billid from deleted )

update customer
set balance = balance - ( select sum(d.price) from deleted d where b.billid = d.billid )
from customer c
join bill b
on b.customerid = c.customerid
where b.billid in ( select billid from deleted )

GO

create trigger trg_treatment_update
on treatment
for update
as
update bill
set total = total
- (select sum(d.price) from deleted d where b.billid = d.billid )
+ (select sum(i.price) from inserted i where b.billid = i.billid )
from bill b
where b.billid in ( select billid from deleted )

update customer
set balance = balance
- ( select sum(d.price) from deleted d where b.billid = d.billid )
+ (select sum(i.price) from inserted i where b.billid = i.billid )
from customer c
join bill b
on b.customerid = c.customerid
where b.billid in ( select billid from deleted )

GO

Misbah

ASKER

thanks alot angelIII :D

the code works fine now.

ynaina

How can we retreive the trigger code which was created?

ynaina

i could see under table folder