Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1098
  • Last Modified:

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
0
Misbah
Asked:
Misbah
  • 4
  • 4
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
create trigger trg_treatment_new
on treatment
for insert
as
  update bill
    set total = total + (select sum(t.price) from inserted where t.billid = i.billid )
  from bill  b
  where b.billid in ( select billid from inserted )

  update customer
    set balance =  balance + ( select sum(t.price) from inserted where t.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(t.price) from deleted where t.billid = d.billid )
  from bill  b
  where b.billid in ( select billid from deleted )

  update customer
    set balance =  balance - ( select sum(t.price) from deleted where t.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(t.price) from deleted where t.billid = d.billid )
                 + (select sum(t.price) from inserted where t.billid = i.billid )

  from bill  b
  where b.billid in ( select billid from deleted )

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

GO






0
 
MisbahAuthor Commented:
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' ... !!!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MisbahAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
MisbahAuthor Commented:
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 : ?

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21871596.html
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
MisbahAuthor Commented:
thanks alot angelIII  :D

the code works fine now.
0
 
ynainaCommented:
How can we retreive the trigger code which was created?
0
 
ynainaCommented:
i could see under table folder
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now