|
|
You're in the
sql server programming
group:Trigger Syntax problem
sql server programming:
Perhaps it's just too late at night but I just can't seem to get this right. I'm trying to create a UPDATE trigger on a table which will update another table.
What I would like to do is declare an INT variable in the trigger and then assign it a value retrieved during a select statement. In general it should be something like this. I've tried a number of variations on this theme but I keep getting errors. I want to test both inserted and deleted for existence of this field and then use both values to update the quantity field in another table. This is the basic idea. CREATE TRIGGER TABLE1_TEST ON dbo.TABLE1 FOR UPDATE AS DECLARE @x INT IF EXISTS (SELECT FIELD1 FROM deleted) BEGIN @x=FIELD1 END ----------------------
It is hard to follow your narrative, but when you say "I want to test
both inserted and deleted for existence of this field [sic] and then use both values to update the quantity field [sic] in another table." I have to ask: 0)Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are. 1) Triggers? SQL is a declarative language, so SQL programmers don't think in terms of procedural code like you are doing. Dynamic SQL, cursors, triggers, long stored procedures, IDENTITY and proprietary datatypes are all signs of poor SQL programming. They are needed less than 2% of the time. 2) Why are you storing a computed value in a second table, instead of using VIEW? That kind of redundancy is what we used to have in file system -- files do have fields and fields do have a PHYSICAL existence, whereas a column is a LOGICAL concept, which may or may not physically exist. In the 1950's and 1960's, we had to do this kind of thing to hold the data and pass it between applications on magnetic tapes. That kind of programming stopped with the first DBMS systems. --CELKO-- *** Sent via Developersdex http://www.developersdex.com ***
I used 'IF EXISTS' because I thought that was the standard way to test if a change had taken place. I based this on the code in Chapter 9, Lesson 3 of the 70-229 book, Implementing Triggers.
Perhaps I simplified my question too much. What I am actually trying to do is update the Quantity In Stock field of an inventory table every time an order detail is added, updated, or deleted . Originally I had 3 separate triggers doing this and they all apparently work fine. What I was trying to do was get one trigger to do it all. This is the current trigger for Delete: ALTER TRIGGER OrderDet_updateInvDel ON dbo.OrderDet FOR DELETE AS DECLARE @Ptype CHAR(1) DECLARE @Pid VARCHAR(12) DECLARE @Qu INT SELECT @Ptype=ProdType,@Pid=ProdID, @Qu=Quantity FROM deleted if @Ptype='P' BEGIN Update Product set QuantityInStock= ( (SELECT QuantityInStock FROM Product WHERE ProdID=@Pid)+@Qu )WHERE ProdID=@Pid END [quoted text, click to view] "David Portas" wrote:
> I'm not clear exactly what the IF EXISTS statement is supposed to be > checking for so I don't know how to rewrite it for you. Use > UPDATE(column_name) rather than EXISTS if you want to determine if a column > has been updated. > > Your use of a local variable looks wrong here. A trigger must be able to > handle multiple row updates so it doesn't normally make sense to assign > updated column values to variables. Never write a trigger that only works > with single-row updates. > > Here's an example of what your trigger possibly should look like: > > CREATE TRIGGER TABLE1_TEST > ON dbo.TABLE1 FOR UPDATE > AS > DECLARE @x INT > > IF UPDATE(col1) > BEGIN > UPDATE Table2 > SET col2 = > (SELECT col1 > FROM Inserted > WHERE keycol = Table2.keycol) > END > > -- > David Portas > SQL Server MVP > -- > >
I've posted a more complete description of what I am trying to do in response to Dave Portas's message.
Not that I want to start a flame war but I really don't get what you are saying, particuarly point 1. Everything I've read, particularly the 70-229 book gives me the impression that Microsoft is doing everything it can to make T-SQL more procedural, more like a 'real programming language'. (BEGIN, END, IF, variables, etc...) [quoted text, click to view] "Joe Celko" wrote:
> It is hard to follow your narrative, but when you say "I want to test > both inserted and deleted for existence of this field [sic] and then use > both values to update the quantity field [sic] in another table." I have > to ask: > > 0)Please post DDL, so that people do not have to guess what the keys, > constraints, Declarative Referential Integrity, datatypes, etc. in your > schema are. > > 1) Triggers? SQL is a declarative language, so SQL programmers don't > think in terms of procedural code like you are doing. Dynamic SQL, > cursors, triggers, long stored procedures, IDENTITY and proprietary > datatypes are all signs of poor SQL programming. They are needed less > than 2% of the time. > > 2) Why are you storing a computed value in a second table, instead of > using VIEW? That kind of redundancy is what we used to have in file > system -- files do have fields and fields do have a PHYSICAL existence, > whereas a column is a LOGICAL concept, which may or may not physically > exist. > > In the 1950's and 1960's, we had to do this kind of thing to hold the > data and pass it between applications on magnetic tapes. That kind of > programming stopped with the first DBMS systems. > > --CELKO-- > > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it!
I'm not clear exactly what the IF EXISTS statement is supposed to be
checking for so I don't know how to rewrite it for you. Use UPDATE(column_name) rather than EXISTS if you want to determine if a column has been updated. Your use of a local variable looks wrong here. A trigger must be able to handle multiple row updates so it doesn't normally make sense to assign updated column values to variables. Never write a trigger that only works with single-row updates. Here's an example of what your trigger possibly should look like: CREATE TRIGGER TABLE1_TEST ON dbo.TABLE1 FOR UPDATE AS DECLARE @x INT IF UPDATE(col1) BEGIN UPDATE Table2 SET col2 = (SELECT col1 FROM Inserted WHERE keycol = Table2.keycol) END -- David Portas SQL Server MVP --
[quoted text, click to view]
>> ... gives me the impression that Microsoft is doing everything it can to make T-SQL more procedural, more like a 'real programming language'.(BEGIN, END, IF, variables, etc...) << Hey, declararive languages are just as REAL! And we're way cooler, too :) MS inherited the T-SQL language from Sybase and cannot get rid of it. If they wanted to add a SQL/PSM, which is the Standard 4GL for SQL, I am not sure the architecture would support it. But in their favor, correcting the predicates, adding infixed join operators and several other things have been very good efforts away from the "Sybase Code Museum" and toward a non-procedural, Standard SQL implementation. They are screwing up with .NET and CLR; what a great way to trap you into Microsoft forever! The CLR programmers will write to the database in their favorite language -- never mind that these 3GL languages all have different math, logic and functions and were never meant to work with sets of data. They will reduce SQL Server to a very slow, very bad file system. Okay, this is an inventory problem according to another posting of yours. It is a hard problem because there are a zillion ways with a dozen variants each to model an inventory. LIFO or FIFO or average cost? Back orders or not? Bulk or individual items? Discounts? Are orders resolved as they are received or at the end a time period? Full or partial shipments? Etc. It is a whole area of business theory in itself. I tend to make a column with the physical quantity on hand separate from the total quantity sold or obligated as of the time that we have to assemble the orders from a pick list. Does the company tries fill the most orders (lots of happy customers!) or the largest orders (best cash flow!) first? Or do back orders come first? This might be more than a "newsgroup problem" ... If you do this with stored procedures instead of triggers, you can have some control that would be impossible otherwise. I can handle shortages, damaged good, returns, substitutions and cancelled orders a good bit easier than if a trigger assumes that an order is automatically handled one and only one way, without human intervention. --CELKO-- =========================== Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are. *** Sent via Developersdex http://www.developersdex.com ***
Ok, everyone remain calm. I think you're both operating at a far higher level than I can understand right now and you've really given me some food for thought.
I think I've fallen into the trap of trying to use a trigger because I want to prove I can write triggers. (When all you have is a hammer, everything looks like a nail.) Plus I seem to have picked up the notion somewhere that this is the sort of thing you do with triggers. With reference to a remark by Portas, I have been thinking only in terms of single record events and I suppose that a multiple record event involving the order detail file could really cause problems. Perhaps what I need to do is simply write the update code into the order detail procedures themselves so that inventory updates are really confined to single record events. (And when I say inventory, I am talking about a very simple-minded system.) (T-SQL was derived from Sybase????? What else haven't I been told?) [quoted text, click to view] "Joe Celko" wrote:
> >> ... gives me the impression that Microsoft is doing everything it can > to make T-SQL more procedural, more like a 'real programming language'. > (BEGIN, END, IF, variables, etc...) << > > Hey, declararive languages are just as REAL! And we're way cooler, too > :) > > MS inherited the T-SQL language from Sybase and cannot get rid of it. > If they wanted to add a SQL/PSM, which is the Standard 4GL for SQL, I am > not sure the architecture would support it. > > But in their favor, correcting the predicates, adding infixed join > operators and several other things have been very good efforts away from > the "Sybase Code Museum" and toward a non-procedural, Standard SQL > implementation. > > They are screwing up with .NET and CLR; what a great way to trap you > into Microsoft forever! The CLR programmers will write to the database > in their favorite language -- never mind that these 3GL languages all > have different math, logic and functions and were never meant to work > with sets of data. They will reduce SQL Server to a very slow, very bad > file system. > > Okay, this is an inventory problem according to another posting of > yours. It is a hard problem because there are a zillion ways with a > dozen variants each to model an inventory. LIFO or FIFO or average > cost? Back orders or not? Bulk or individual items? Discounts? Are > orders resolved as they are received or at the end a time period? Full > or partial shipments? Etc. It is a whole area of business theory in > itself. > > I tend to make a column with the physical quantity on hand separate from > the total quantity sold or obligated as of the time that we have to > assemble the orders from a pick list. Does the company tries fill the > most orders (lots of happy customers!) or the largest orders (best cash > flow!) first? Or do back orders come first? > > This might be more than a "newsgroup problem" ... > > If you do this with stored procedures instead of triggers, you can have > some control that would be impossible otherwise. I can handle shortages, > damaged good, returns, substitutions and cancelled orders a good bit > easier than > if a trigger assumes that an order is automatically handled one and only > one way, without human intervention. > > --CELKO-- > =========================== > Please post DDL, so that people do not have to guess what the keys, > constraints, Declarative Referential Integrity, datatypes, etc. in your > schema are. > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it!
Ok, everyone remain calm. I think you're both operating at a far higher level than I can understand right now and you've really given me some food for thought.
I think I've fallen into the trap of trying to use a trigger because I want to prove I can write triggers. (When all you have is a hammer, everything looks like a nail.) Plus I seem to have picked up the notion somewhere that this is the sort of thing you do with triggers. With reference to a remark by Portas, I have been thinking only in terms of single record events and I suppose that a multiple record event involving the order detail file could really cause problems. Perhaps what I need to do is simply write the update code into the order detail procedures themselves so that inventory updates are really confined to single record events. (And when I say inventory, I am talking about a very simple-minded system.) (T-SQL was derived from Sybase????? What else haven't I been told?) [quoted text, click to view] "Joe Celko" wrote:
> >> ... gives me the impression that Microsoft is doing everything it can > to make T-SQL more procedural, more like a 'real programming language'. > (BEGIN, END, IF, variables, etc...) << > > Hey, declararive languages are just as REAL! And we're way cooler, too > :) > > MS inherited the T-SQL language from Sybase and cannot get rid of it. > If they wanted to add a SQL/PSM, which is the Standard 4GL for SQL, I am > not sure the architecture would support it. > > But in their favor, correcting the predicates, adding infixed join > operators and several other things have been very good efforts away from > the "Sybase Code Museum" and toward a non-procedural, Standard SQL > implementation. > > They are screwing up with .NET and CLR; what a great way to trap you > into Microsoft forever! The CLR programmers will write to the database > in their favorite language -- never mind that these 3GL languages all > have different math, logic and functions and were never meant to work > with sets of data. They will reduce SQL Server to a very slow, very bad > file system. > > Okay, this is an inventory problem according to another posting of > yours. It is a hard problem because there are a zillion ways with a > dozen variants each to model an inventory. LIFO or FIFO or average > cost? Back orders or not? Bulk or individual items? Discounts? Are > orders resolved as they are received or at the end a time period? Full > or partial shipments? Etc. It is a whole area of business theory in > itself. > > I tend to make a column with the physical quantity on hand separate from > the total quantity sold or obligated as of the time that we have to > assemble the orders from a pick list. Does the company tries fill the > most orders (lots of happy customers!) or the largest orders (best cash > flow!) first? Or do back orders come first? > > This might be more than a "newsgroup problem" ... > > If you do this with stored procedures instead of triggers, you can have > some control that would be impossible otherwise. I can handle shortages, > damaged good, returns, substitutions and cancelled orders a good bit > easier than > if a trigger assumes that an order is automatically handled one and only > one way, without human intervention. > > --CELKO-- > =========================== > Please post DDL, so that people do not have to guess what the keys, > constraints, Declarative Referential Integrity, datatypes, etc. in your > schema are. > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it!
[quoted text, click to view]
>> .. think I've fallen into the trap of trying to use a trigger because I want to prove I can write triggers. (When all you have is a hammer,everything looks like a nail.) << Probably more like "new toy" syndrome than "old hammer" syndrome, but both are deadly :) [quoted text, click to view] >> Plus I seem to have picked up the notion somewhere that this is the sort of thing you do with triggers. With reference to a remark byPortas, I have been thinking only in terms of single record [sic] events and I suppose that a multiple record [sic] event involving the order detail file could really cause problems. << Bad way to look at a set-oriented language. Triggers are for when the same complex situation ALWAYS, NOW AND FOREVER WITHOUT EXCEPTION, has the same solution/rule. TRIGGERs are also a "code museum" relic from the Sybase days. [quoted text, click to view] >> Perhaps what I need to do is simply write the update code into the order detail procedures themselves so that inventory updates are reallyconfined to single record [sic] events. (And when I say inventory, I am talking about a very simple-minded system.) << Simple LIFO or simple FIFO? Bulk or item inventory? There are a lot of simple inventory models just based on just quantity on hand. [quoted text, click to view] >> (T-SQL was derived from Sybase????? What else haven't I been told?) <<Do you know The Secret Handshake? --CELKO-- =========================== Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are. *** Sent via Developersdex http://www.developersdex.com ***
Your Delete Trigger would only work for a single row delete - it can't
handle multiple rows. Don't write triggers that way. In this case it seems unlikely that a trigger is the best solution for you. Drop the redundant QuantityInStock column, drop the triggers and just summarize deliveries and receipts in a query or view something like this: SELECT /* other columns from Product ... */, COALESCE(Q.quantityinstock,0) AS quantityinstock FROM Product AS P LEFT JOIN (SELECT prodid, SUM(quantity) FROM OrderDet /* Orders and receipts table ? */ GROUP BY prodid) AS Q(quantityinstock) ON P.prodid = Q.prodid (untested) For completeness here's the INSERT, UPDATE, DELETE version of the trigger: CREATE TRIGGER trg_OrderDet ON OrderDet FOR INSERT, UPDATE, DELETE AS UPDATE Product SET quantityinstock = quantityinstock + (SELECT COALESCE(SUM(quantity),0) FROM Deleted WHERE prodid = Product.prodid) - (SELECT COALESCE(SUM(quantity),0) FROM Inserted WHERE prodid = Product.prodid) WHERE EXISTS (SELECT * FROM Inserted WHERE prodid = Product.prodid UNION ALL SELECT * FROM Deleted WHERE prodid = Product.prodid) -- David Portas SQL Server MVP --
[quoted text, click to view]
> Microsoft is doing everything it can to make T-SQL more procedural, > more like a 'real programming language'. (BEGIN, END, IF, variables, > etc...) It's true that *T-SQL* has many procedural elements but Joe's valid point is that the vast majority of your data manipulation can and should be achieved through the standard set-based *SQL* (INSERT, UPDATE, DELETE, SELECT) statements. If you find yourself writing a lot of procedural code then that may be an indication that you have some weak elements of design (such as redundant columns or lack of keys) which you have to work around with non set-based code. In SQL Server 2005, Microsoft has put the emphasis on developing and extending the set-based SQL elements of T-SQL rather than the procedural control language. T-SQL isn't intended to be a fully featured programming language. -- David Portas SQL Server MVP --
[quoted text, click to view]
> Perhaps what I need to do is simply write the update code into the > order detail procedures themselves so that inventory updates are > really confined to single record events. Why would you want to impose a single-row limitation *anywhere* in your code? I guarantee that if you do that you'll only end up rewriting it in the near future - probably when you need to do some administrative maintenance and find that you would otherwise need to change 100s or 1000s of rows individually. It's simply good DB design to write set-based rather than row-by-row code - that's how RDBMSs work and that's what SQL is designed for. [quoted text, click to view] > (T-SQL was derived from Sybase????? What else haven't I been told?) http://www.pulagara.com/sqlserver/evolution.htm -- David Portas SQL Server MVP --
[quoted text, click to view]
> TRIGGERs are also a "code museum" relic from > the Sybase days. Two common uses for triggers are for enforcing DRI between multiple databases or for validating complex constraints that can't be declared as CHECK or FOREIGN KEY. The latter case we will have with us until SQLServer supports full SQL92-style constraints. -- David Portas SQL Server MVP --
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |