We have a few issues that we hope to get some information on as follows. We are not sql gurus and are learning on the fly (not the best, but we have to deal with this on a day by day basis while we interview SQL dbs).

A) We have a DB that looks like this, we inherited this from a company that we just bought, so we need to keep this intact until we move to another application development platform and redesign the DB,

B) Here is the partial schema:

A) DB: Customer_Activities (there are more tables etc, but I think this is the basics that would be needed to understand the problem):
B) Customers: customerno (PK), purchase_no (idx), total_purchase, no_purchases (int), other columns
C) Purchases: purchase_no (pk), customerno (idx-dupes), item_qty (int), item_cost (float), item_total (float) other columns,

relationship looks like this: customers -> purchases (1:many) on customerno.

Our current VB app performs the following:

1) when a new customer is added, the total_purchases is set to 0.00, no_purchases is set to 0 (zero),
2) when a customer makes a purchase, the following is performed "in the app",
    a) item_total is calculated as item_qty * item_cost,
    b) item_total is added to [customers].[total_purchases],
    c) item_qty is added to [customers].[no_purchases],

As we are looking at moving to a different dev platform (such as vb.net or Delphi etc), we would like to have sql2k5 perform these calculations via triggers. I personally don't think the app should be doing this anyway. :)

So we would like to have a trigger(s) on purchases that does the following (there would never be more than on record inserted/updated or deleted at a time):

i) adds the value from 2-b above to the total_purchase in Customers,
ii) adds the value from 2-c above to no_purchases in Customers,
iii) if a value in purchases.item_qty or item_cost is changed, this needs to be recalculated in the trigger (as an update I would suspect). Then this would have to be reflected in customers.total_purchases and no_purchases.

Another thing this app doesn't take into account is if a customers purchase is deleted, then there are inaccurate totals left in the appropriate customers columns (total_purchases and no_purchases). We think that the out-going db was doing this via his query tool or something similar (he's not cooperative in a few areas) and documentation is sparse if not incomplete. Very frustrating.

We've tried several different triggers and have the following problems and just can't seem to get the result we need. We do have a few triggers that update total_purchases (when added), but nothing for deletion or change.

I do realize that this is a lot to ask, and as mentioned above the whole thing needs a redesign (which hopefully a new SQL db can do for us), but we need something for the interim.

I've spent the better part of 2 days scouring the web and have made some progress, but alas not nearly enough. I've purchased several resource books for SQL but just don't have the time to spend on this as much as needed at this point.

Any help or suggestions (pointers to web sites etc), is "hugely" appreciated.

I can give more information if needed, but I hope this is basically enough to give you all an idea as to where we are at.

If anyone can suggest a "good" source for DB admins, we would appreciate that as well). Monster, Workopolis etc) have far too many submissions to review at this time.

Thanks in advance Rodger.

This should be close to what you need. It could all be done in a single
trigger but three triggers may be easier to understand.

Be sure to check my math in case I did not fully understand your question.

Happy Thanksgiving

Tom

SET NOCOUNT ON;


IF EXISTS (SELECT 1 FROM  SYS.OBJECTS WHERE  OBJECT_ID =
OBJECT_ID('dbo.Customers') AND TYPE = 'U ')
BEGIN    drop table dbo.Customers;
END
GO
create table dbo.Customers
(    customerno        varchar(255)    primary key
    ,purchase_no    int
    ,total_purchase    float            default 0.0
    ,no_purchases    int                default 0.0
);
go
IF EXISTS (SELECT 1 FROM  SYS.OBJECTS WHERE  OBJECT_ID =
OBJECT_ID('dbo.Purchases') AND TYPE = 'U ')
BEGIN    drop table dbo.Purchases;
END
GO
go
create table dbo.Purchases
(    purchase_no        int identity primary key
    ,customerno            varchar(255)
    ,item_qty            int
    ,item_cost            float   
    ,item_total            float
)
go
IF EXISTS (SELECT 1 FROM  SYS.OBJECTS WHERE  OBJECT_ID =
OBJECT_ID('dbo.PurchasesInsert') AND TYPE = 'TR')
BEGIN    DROP TRIGGER dbo.PurchasesInsert;
END
GO
create trigger dbo.PurchasesInsert
    on    dbo.Purchases for insert
as
UPDATE    dbo.Customers
    SET    total_purchase  =    INSERTED.item_total
        ,no_purchases    =    INSERTED.item_qty
FROM    INSERTED
JOIN    dbo.Customers ON INSERTED.customerno = Customers.customerno;
return;
go
IF EXISTS (SELECT 1 FROM  SYS.OBJECTS WHERE  OBJECT_ID =
OBJECT_ID('dbo.PurchasesUpdate') AND TYPE = 'TR')
BEGIN    DROP TRIGGER dbo.PurchasesUpdate;
END
GO
create trigger dbo.PurchasesUpdate
    on    dbo.Purchases for update
as
UPDATE    dbo.Customers
    SET    total_purchase  =    total_purchase    +    (INSERTED.item_total    -
    DELETED.item_total)
        ,no_purchases    =    no_purchases    +    (INSERTED.item_qty        - DELETED.item_qty)
FROM    INSERTED
JOIN    dbo.Customers ON INSERTED.customerno = Customers.customerno
JOIN    DELETED          ON INSERTED.customerno = DELETED.customerno;
return;
go
IF EXISTS (SELECT 1 FROM  SYS.OBJECTS WHERE  OBJECT_ID =
OBJECT_ID('dbo.PurchasesDelete') AND TYPE = 'TR')
BEGIN    DROP TRIGGER dbo.PurchasesDelete;
END
GO
create trigger dbo.PurchasesDelete
    on    dbo.Purchases for delete
as
UPDATE    dbo.Customers
    SET    total_purchase  =    total_purchase    - DELETED.item_total
        ,no_purchases    =    no_purchases    - DELETED.item_qty
FROM    DELETED
JOIN    dbo.Customers ON DELETED.customerno = Customers.customerno;
return;
go

INSERT dbo.Customers (customerno) VALUES('Cust 1');
SELECT * FROM dbo.Customers;

INSERT dbo.Purchases (customerno, item_qty, item_cost, item_total) VALUES
('Cust 1', 1, 2.00, 2.00);
SELECT * FROM dbo.Customers;
UPDATE dbo.Purchases
    SET  item_qty = 2
        ,item_total = 4
WHERE customerno = 'Cust 1';
SELECT * FROM dbo.Customers;

DELETE dbo.Purchases WHERE customerno = 'Cust 1';
SELECT * FROM dbo.Customers;

Hello,
  You should check out Dataphor as well @
  www.dataphor.org
  And
  www.databaseconsultinggroup.com/index.html

  Note I have no connection to the above but use and advocate the Dataphor system for application development.
  Dataphor can use sql server for the data repository and can easily communicate with it.

  Sql server triggers are a poor choice for application development. Dataphor will completely take over all these functions for you. Dataphor triggers (events) are 'row' oriented and there are true 'before' , 'after' and instead of options. In effect you'll do all your development in Dataphor
  and let sql server take are of data storage and access.

  More on Dataphor (with many code samples) on my blog:
  www.beyondsql.blogspot.com

  best,
  steve

Exactly why are SQL Server triggers a poor choice?


Tom

Hi,

I think these articles, by someone who works at MS, sum up the current state
of MS triggers (and more),

The Trouble with Triggers
http://www.sqlskills.com/blogs/conor/default.aspx

CHECK Constraints vs. TRIGGERS
http://blogs.msdn.com/conor_cunningham_msft/default.aspx

The vote is in! (CHECK CONSTRAINTS vs. TRIGGERS vs. STORED PROCEDURE
business logic)
http://blogs.msdn.com/conor_cunningham_msft/default.aspx

I'm not even going into their implementation, it's the nature of MS triggers
that is the problem. Applications should be developed with the idea that it
should be a set based exercise because that is what MS triggers are best
suited for. A classic case of the tail wagging the dog. That application
development should fit neatly into the framework of sql server is
ridiculous. AD involves granularity which is either missing in the MS
trigger or is so obtuse it turns developers off. AD involves the idea of a
'row'. There is no concept of a row anywhere in sql server. A set, a table
is NOT a row. I agree  there's a multi-row consideration. And I consider it
ridiculous. The idea that an application is an event driven excercise has no
built in intent in sql server. And the problems extend out from triggers.
The constraint in sql server is itself a constraint to AD. It's far to
immature to be of use to enterprise apps. It would seem that those at MS who
work on sql server are not application developers. Those that are work on
Linq. What's wrong with this picture?:) Too many sql experts are neophytes
in AD. And too many sql experts have not worked on any other system. So they
cannot see themselves arguing to fit a round peg into a square whole:)
Sql server is a kewl sixteen wheeler that can haul a_s down a superhighway.
It's for long hauls and heavy loads. But you don't take a 16 wheeler to Le
Mans. There you need a finely tuned and detailed thoroughbred than can
maneuver in tight and small quarters. With Dataphor/sql server you get the
benefit of each:) Try it and see for yourself. You only have to pay for
one:)

(A prior comment on triggers including Conors blog on triggers:
http://beyondsql.blogspot.com/2008/07/demoralization-by-trigger.html)

www.beyondsql.blogspot.com

best,
steve

<newscorrespond***@charter.net> wrote in message
Show quoteHide quote

Thank you all for the detailed information and insights.

As for now, we are looking at using and implementing the code fragments and
examples that were presented by Tom. When we have engaged a DB, we will
certainly look at the other options presented by Steve. I don't want to
complicate this scenario more that what I need to at this point.

Again, I thank everyone for their time, patience and contributions.

Rodger.

Show quoteHide quote

Show quote Hide quote

I just wanted to reply to Tom's post specifically.

I went over your code and it's pretty much what we were trying to do. What
we didn't perform in all cases was the appropriate JOINS on the tables. For
example, when we wanted to decrement total purchases, we didn't join
anything to anything which explains why nothing was changed.

Not sure why the "Return" statement is there, but we left it in.

The help is greatly appreciated.

Thank you.

Show quoteHide quote

The return statement is a left over habit. A long time ago when MS SQL was
being born it was a Sybase database in a Microsoft box. I worked in the
Sybase Advanced Systems Group. Sybase recomended a return statement after
all procedures and a trigger is a kind of procedure. It is not necessary and
could be removed.

I looked over those triggers again and there perhaps are two more statements
that should have been included. So much for coding on a holiday.



IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;


The "IF" statement bypasses the trigger if no rows were updated.

The SET NOCUNT ON; eliminates rowcounts from flowing through the network.
You should do this in every procedure unless you specifically know that the
rowcount message is expected by the client.
The "IF" statement must be before the SET statement because the SET
statement will reset @@ROWCOUNT.



Have a great week-end.

Tom

<newscorrespond***@charter.net> wrote in message
I'm sorry but the trigger must have been designed while partying. I can only
assume the MS group was invited too:(:)

Thanks Tom. As well, you have a good one.

Rodger
<newscorrespond***@charter.net> wrote in message
Show quoteHide quote

Hi again Tom. I understand your comments, however the question that comes to
mind is, if a Trigger were being fired, would that not suggest that there
were/are rows being updated?

I can think of a situation that wouldn't and that would be if one were
rolling back a transaction, at least that's what I am getting from the SQL
programmers books that I bought - could be out to lunch here tho.

Again, thanks.

<newscorrespond***@charter.net> wrote in message
Show quoteHide quote

UPDATE SOMETABLE
    SET SOMECOLUMN = 123
WHERE SOME CONDITION IS NEVER MET.

This would result in no rows being updated. This would cause the trigger to
fire. A similar statement could cause DELETE to fire a trigger with no
deletes. Inserting rows with an INSERT .... SELECT.... WHERE could also
cause this to happen.

It may be useful to do something when this condition occurs particularly in
a test environment. You could notify the application what was going on and
perhaps the application could save a trip to the server.

The trigger is fired because an INSERT, UPDATE or DELETE was executed
whether or not anything was updated.

There is one other condition you might want to be aware of.

Replication will cause an additional update on the rows. You may not want
the work of your trigger to be done twice. If you only want parts of the
trigger to be run one time for each INSERT, DELETE or UPDATE you need to
check the nest level and not run if it is greater than 1.

IF TRIGGER_NESTLEVEL(object_ID('TRIGGER NAME')) > 1 RETURN;


Tom

On Wed, 3 Dec 2008 12:56:48 -0700, Rodger Dultrie (not the actor) wrote:

(snip)
Hi Rodger,

In addition to Tom's excellent reply, may I suggest dumping that book?
No triggers are fired during a rollback. Any changes made during the
trigger execution are in the log file, so when SQL Server undoes
everything in the log file as part of the ROLLBACK, all effects of the
trigger are automatically undone.

Thank you both Tom and Hugo. The explanation is, as usual, quite complete
and very informative.

Book Dumped.

Cheers.

Show quoteHide quote

Show quote Hide quote Also sometimes triggers do not fire:

www.devx.com/dbzone/Article/31985/0/page/2

My apologies for my oversight. I read your kewl article and should have
included it:(

www.devx.com/dbzone/Article/31985/0/page/2

cmdshell how to run server side vbscript
Issue with subselect
Lumigent Log Explorer - where to get it?
INSERT'ing from SQL Server into DB2
HTTP T-SQL queries (with XML support) don't work on sql server 200
Retaining same Instance Name in side-by-side upgrade/migration
Connections between 2 SQL Servers
issue with data format in joining tables
can u help me in populating random data more than 32767 rows
free text search in large starschemes