October 8, 2007 at 7:15 am
i want to update a date field whenever another field is updated. is this good? it will be triggered ONLY when the field iProrogation is set to 1, right? so i wont have performance issues :blush:
tx for u advices!
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Prorogation] ON [neolane].[NmsDelivery]
AFTER UPDATE
as
BEGIN
SET NOCOUNT ON;
if UPDATE(iProrogation)
begin
Update NmsDelivery set tsProrogation = getDate(),
iProrogation = 0
FROM inserted
WHERE inserted.iDeliveryId = NmsDelivery.iDeliveryId
and inserted.iProrogation = 1
end
END
October 8, 2007 at 7:46 am
It looks fine 🙂 (well, of course when it comes to looks, it is interesting to see mixed casing, END and end :P)
It will be called once assuming that you have left the RECURSIVE_TRIGGERS setting.
Regards,
Andras
October 8, 2007 at 8:01 am
:Whistling: yes, I will correct the looks 😉
and yes, recursive trigger option is set off!
tx again!!!
October 8, 2007 at 9:01 am
I agree with Andras that it looks ok from a code perspective, but I'd format it to make it read a little better.
I'd do it like this:
CREATE TRIGGER [Prorogation] ON [neolane].[NmsDelivery]
AFTER UPDATE
as
BEGIN
SET NOCOUNT ON;
IF UPDATE(iProrogation)
BEGIN
UPDATE NmsDelivery
set tsProrogation = getDate()
, iProrogation = 0
FROM inserted i
WHERE i.iDeliveryId = NmsDelivery.iDeliveryId
and inserted.iProrogation = 1
END
RETURN
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 8, 2007 at 9:07 am
It looks good to me also. I'm surprised we haven't seen a post in this thread about how no trigger is a good trigger:D. Not that I would say that, but I have read it several times.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 8, 2007 at 9:11 am
Steve, you have started now a very very long thread 🙂
So to add to your style improvement (which is nicely indented), here is somehting that has qualified object names, and uppercased keywords 🙂
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER neolane.Prorogation ON neolane.NmsDelivery
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON ;
IF UPDATE(iProrogation)
BEGIN
UPDATE neolane.NmsDelivery
SET tsProrogation = GETDATE()
, iProrogation = 0
FROM inserted
WHERE inserted.iDeliveryId = NmsDelivery.iDeliveryId
AND inserted.iProrogation = 1
END
END
Hmm, but the doublespacing seems to be coming from the forum's code tag 🙁
Andras
October 11, 2007 at 12:09 pm
You mention that no trigger is a good trigger.
I suppose you mean because of the possible performance problems.
I've 2 applications in which users can insert/delete and update their own orders table.
(Primary key : Orderno,lineno,Itemno,quantity,price)
There is a 3e application in which another users checks the entered orderlines en modifies when needed.
My solution for the moment is a trigger which will launch on update/insert and delete and will delete
the "old" version and replaces it with the new version of the orderline, in a third table.
(Primary key : ApplicationID,Orderno,lineno,Itemno,quantity,price)
The changes which are made in the third table are transferred to the original orderline by manual coding. (no trigger)
For the moment the database is small, but it will increase rapidly.
Is there a better solution for this ?
Greetings
Bart
October 11, 2007 at 1:13 pm
My "no trigger is a good trigger" comment was meant to be facetious. There are those who believe triggers are "hidden" code and should be avoided at all costs. I personally like triggers even though I have been bitten by them when I working on someone else's database and not knowing they were there. Also when they are poorly written, like any SQL code, they can cause performance problems and data integrity issues.
As far as your situation, I am not sure I understand what you are doing. It appears you have an order items/lines table where records are created, then when someone else edits them you are copying them to a new table with an additional column for application id, deleting the original record, allowing updates, then copying it back into the original table. Something like this:
User A using application A creates an order (1) in Table A (insert) -> insert trigger fires and creates a record in Table B adding Application A to the record.
User B accesses order 1 in Table A using application B and changes the quantity (update) -> update trigger fires creating a record in Table B adding Application B to the record and deletes the record in Table A (Delete) -> Delete trigger fires. Then a manual process moves the record from Table B back to Table A (Insert) -> Insert Trigger fires.
If I am even close to correct then I don't think the design is right. Can you post the business requirements and current DDL?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 11, 2007 at 7:49 pm
Jack Corbett (10/8/2007)
It looks good to me also. I'm surprised we haven't seen a post in this thread about how no trigger is a good trigger:D. Not that I would say that, but I have read it several times.
Heh... I was just getting around to that 😀
--Jeff Moden
October 12, 2007 at 12:48 pm
I try to give a better explanation.
There are two companies who are in the same companygroup who are both selling the same product.
Each company has it's own VAT number and is completely independent but there is only
1 production department which works for both of them.
Each company has it's own application at this moment in which the orders, placed by the customer,
are put into. I have 2 different databases in SQL with their own Orderdetail table.
One for each company. The most important fields are available in both tables.
Since both companies are doing the same business they have only 1 production environment and the planning is made based on the order which are put in on both Orderdetail tables.
The head chef of the planning department wants to see all the orders of both companies, in detail in 1 overview list on screen and wants to be able to change the order when needed.
That's why I created a new third orderline table with a direct link to the original order table.
Company A : OrderID, OrderLine, ItemNo, Quantity, Unit price
Company B : OrderID, OrderLine, ItemNo, Quantity, Unit price
New Table C : CompanyID,OrderID,OrderLine,ItemNo,Quantity,Unit price.
Since the users at both companies are constantly entering orders, and the head chef wants to see the changes immediately on his planningboard, I created a trigger on both Orderdetail tables which will copy the orderdetail records in the new Table C.
Now I've got what I wanted: The head chef sees the orderdetail changes right away. But now he also wants to change the orderline on this planboad (on screen). (If this wasn't the case I could simply use a view instead of this third table, I know this)
Since he's working on New Table C, I wrote a little program which finds the original record in Orderdetailline in Company A (or B) and makes the same changes.
My question was, (but I wasn't very clear) is there is an easier way to do this. I also wanted to use another trigger on the New Table C to update the source orderdetail records but i wasn't sure how to avoid an endless loop. I could disable nested triggers i guess but will this work ?
Thanks
Bart
October 12, 2007 at 5:51 pm
No, no... Unless I'm totally misreading this, a good ol' fashioned partitioned view will do all of that without the overhead of triggers and the extra table. It'll be updateable, as well.
--Jeff Moden
October 13, 2007 at 1:16 pm
Can you explain a bit or point me to an article ?
I'm really just starting to get to know SQL
Thank You
October 13, 2007 at 4:00 pm
Sure... A thing called "Books Online" should become your best friend. One way of getting to it is to open Query Analyzer, click on the [Help] button, and select [Transact-SQL Help] from that pull-down menu.
Lookup "partitioned views" in the Index... read about it there... especially the part about "Updateable Partitioned Views".
--Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply