HOW TO CREATE TRIGGER ?

[SAEED BASUDAN]

Hi

i have 2 Table
first one : Customer
with 4 Fields : cst_no,cst_name,total_Debit,tot_credit
second one : Transaction
with 5 Fields : Trns_no,Trns_Date,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]

SAEED BASUDAN (BA*****@YAHOO.COM) writes:

i have 2 Table
first one : Customer
with 4 Fields : cst_no,cst_name,total_Debit,tot_credit
second one : Transaction
with 5 Fields : Trns_no,Trns_Date,cst_no,debit,credit

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

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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Portas]

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_Transaction_Totals
AS
SELECT C.cust_no, C.cust_name,
COALESCE(T.total_debit,0) AS total_debit,
COALESCE(T.total_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]

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" <BA*****@YAHOO.COM> wrote in message
news:49**************************@posting.google.c om...

Hi

i have 2 Table
first one : Customer
with 4 Fields : cst_no,cst_name,total_Debit,tot_credit
second one : Transaction
with 5 Fields : Trns_no,Trns_Date,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

[SAEED BASUDAN]

"Chuck Conover" <cc******@commspeed.net> wrote in message news:<10***************@news.commspeed.net>...

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" <BA*****@YAHOO.COM> wrote in message
news:49**************************@posting.google.c om...

Hi

i have 2 Table
first one : Customer
with 4 Fields : cst_no,cst_name,total_Debit,tot_credit
second one : Transaction
with 5 Fields : Trns_no,Trns_Date,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

To :
Erland Sommarskog
David Portas
Chuck Conover

thank you, thank you, thank you soooooo much.

I am very happy