27. april 2004 - 13:32Der er
12 kommentarer og 1 løsning
Instead of insert trigger
Hej jeg er ved at implementere en trigger i min database, som har til formål at tjekke om en ident allerede findes. Hvis den gør skal denne bare updateres og hvis ikke skal den indsættes i tabellen! jeg har prøvet et par forskellige forsøg:
CREATE TRIGGER add_to_cart ON ta_shoppingcart INSTEAD OF INSERT AS begin IF Exists(Select CartID From ta_shoppingcart where (ArticleID = (inserted.ArticleID)) AND (CartID =(inserted.CartID))) UPDATE ta_shoppingcart SET Quantity = Inserted.Quantity where inserted.cartID = CartID and inserted.AticleID = ArticleID ELSE INSERT ta_shoppingcart(CartID,Quantity,ArticleID,OrderDate) select CartID,Quantity,ArticleID,OrderDate from inserted end
CREATE TRIGGER add_to_cart ON ta_shoppingcart INSTEAD OF INSERT AS begin IF not Exists(Select CartID From ta_shoppingcart where(ArticleID = (inserted.ArticleID)) AND (CartID =(inserted.CartID))) UPDATE ta_shoppingcart SET Quantity = Inserted.Quantity where (((inserted.cartID) = CartID) and ((inserted.AticleID) = ArticleID)) ELSE INSERT INTO ta_shoppingcart(CartID,Quantity,ArticleID,OrderDate) values (inserted.CartID,inserted.Quantity,inserted.ArticleID,inserted.OrderDate) end
i sonly doing what the orioginla = is only doing what the original
The trigger occurs when you either try altering (UPDATE trigger) or inserting (INSERT trigger) a record. Whey do you need to do exactly the same action AGAIN in the trigger? Or am I missing something?
? danegreed, a TRIGGER only gets fired when you either INSERT, UPDATE or DELETE a record. You do NOT need to use a trigger to put the data into the table.
What programming langauge are you using?
Normally if you do things correctly you will know if it is a new records (INSERT) or an existing record (UPDATE) and then in your code you will either INSERT or UPDATE the record, there is NO need to use a trigger for this. And infact I think you will get an error if you try inserting a record which already exists, thats if your table has a primary key. And using a trigger wil not help!
I am using C#, and yes I get an error when trying to insert an existing record, thats why i need the trigger! But can u tell me why the above code do not work? and how to make it work?
danegreed>There is no need to reject my answers until you are finished with the question.
What I am trying to tell you is you should NOT try and use a trigger to get around a problem which is caused because of bad programming!
I know almost NOTHING about C# but I do know that you should try finding out if the record exists before you INSERT it. If it exists then make an UPDATE instead.
Try reading the record first and if you cant find it then it does not exist (then INSERT) if it does exist then UPDATE.
The INSERT trigger will NOT fire (work) if you try inserting a record which already exists, you will get an error. So it is POINTLESS making a trigger!
I think you should try looking at stored procedures, I am almost 100% sure that you can NOT do this with triggers!
In a stored procedure you can check to see if the record exists and UPDATE and if it does NOT exist then you insert! This would be a MUCH better alternative.
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.