HOW TO CREATE TRIGGER ?

[SAEED BASUDAN]

Hi

i have 2 Table
first one : Customer
with 4 Fields : cst_no,cst_name ,total_Debit,to t_credit
second one : Transaction
with 5 Fields : Trns_no,Trns_Da te,cst_no,debit ,credit

MY QUESTION:
HOW TO CREATE TRIGGER FOR UPDATE TOT_DEBIT AND TOT_CREDIT FILEDS IN
CUSTOMER TABLE FROM Transaction TABLE

Thank you

[Erland Sommarskog]

Re: HOW TO CREATE TRIGGER ?

SAEED BASUDAN (BASUDAN@YAHOO. COM) writes:[color=blue]
> i have 2 Table
> first one : Customer
> with 4 Fields : cst_no,cst_name ,total_Debit,to t_credit
> second one : Transaction
> with 5 Fields : Trns_no,Trns_Da te,cst_no,debit ,credit
>
> MY QUESTION:
> HOW TO CREATE TRIGGER FOR UPDATE TOT_DEBIT AND TOT_CREDIT FILEDS IN
> CUSTOMER TABLE FROM Transaction TABLE[/color]

Something like:

CREATE TRIGGER transtri ON Transaction FOR INSERT AS

UPDATE Customer
SET total_Debit = c.total_Debit + t.debit,
total_Credit = c.total_Credit + t.credit
FROM Customer c
JOIN (SELECT cst_no, debit = SUM(debit), credit = SUM(credit)
FROM inserted
GROUP BY cst_no) as t ON c.cst_no = t.cst_no

For simplcity, I have assumed that transactions are only added, never
updated or deleted.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[David Portas]

Re: HOW TO CREATE TRIGGER ?

Rather than attempting to use a trigger, a simpler solution might be to use
a view. I would redesign your tables something like this:

CREATE TABLE Customers (cust_no INTEGER PRIMARY KEY, cust_name VARCHAR(40)
NOT NULL UNIQUE)

CREATE TABLE Transactions (trns_no INTEGER PRIMARY KEY, trns_date DATETIME
NOT NULL, cust_no INTEGER NOT NULL REFERENCES Customers (cust_no), amount
NUMERIC (10,2))

Then create a view like this:

CREATE VIEW Customer_Transa ction_Totals
AS
SELECT C.cust_no, C.cust_name,
COALESCE(T.tota l_debit,0) AS total_debit,
COALESCE(T.tota l_credit,0) AS total_credit
FROM
Customers AS C
LEFT JOIN
(SELECT cust_no,
SUM(CASE WHEN amount<0 THEN amount END) AS total_debit,
SUM(CASE WHEN amount>0 THEN amount END) AS total_credit
FROM Transactions
GROUP BY cust_no) AS T
ON C.cust_no = T.cust_no

--
David Portas
------------
Please reply only to the newsgroup
--

[Chuck Conover]

Re: HOW TO CREATE TRIGGER ?

SAEED,
David Portas solution to use a view is a much better idea than using a
trigger to update your customer table. Triggers of this type can really
slow down your database when doing inserts and updates to a large database.
You might take a look at our videos on triggers and optimization at
www.technicalvideos.net.
Best regards,
Chuck Conover

https://www.TechnicalVideos.net

"SAEED BASUDAN" <BASUDAN@YAHOO. COM> wrote in message
news:49851b43.0 401240902.4f5e1 c10@posting.goo gle.com...[color=blue]
> Hi
>
> i have 2 Table
> first one : Customer
> with 4 Fields : cst_no,cst_name ,total_Debit,to t_credit
> second one : Transaction
> with 5 Fields : Trns_no,Trns_Da te,cst_no,debit ,credit
>
> MY QUESTION:
> HOW TO CREATE TRIGGER FOR UPDATE TOT_DEBIT AND TOT_CREDIT FILEDS IN
> CUSTOMER TABLE FROM Transaction TABLE
>
> Thank you[/color]

[SAEED BASUDAN]

Re: HOW TO CREATE TRIGGER ?

"Chuck Conover" <cconover@comms peed.net> wrote in message news:<107505296 7.854544@news.c ommspeed.net>.. .[color=blue]
> SAEED,
> David Portas solution to use a view is a much better idea than using a
> trigger to update your customer table. Triggers of this type can really
> slow down your database when doing inserts and updates to a large database.
> You might take a look at our videos on triggers and optimization at
> www.technicalvideos.net.
> Best regards,
> Chuck Conover
> www.TechnicalVideos.net
>
>
>
>
>
> "SAEED BASUDAN" <BASUDAN@YAHOO. COM> wrote in message
> news:49851b43.0 401240902.4f5e1 c10@posting.goo gle.com...[color=green]
> > Hi
> >
> > i have 2 Table
> > first one : Customer
> > with 4 Fields : cst_no,cst_name ,total_Debit,to t_credit
> > second one : Transaction
> > with 5 Fields : Trns_no,Trns_Da te,cst_no,debit ,credit
> >
> > MY QUESTION:
> > HOW TO CREATE TRIGGER FOR UPDATE TOT_DEBIT AND TOT_CREDIT FILEDS IN
> > CUSTOMER TABLE FROM Transaction TABLE
> >
> > Thank you[/color][/color]

To :
Erland Sommarskog
David Portas
Chuck Conover

thank you, thank you, thank you soooooo much.

I am very happy