Avatar billede danegreed Nybegynder
27. april 2004 - 13:32 Der 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

er der nogen der kan hjælpe??
Avatar billede terry Ekspert
27. april 2004 - 19:41 #1
I'm a little puzzled as to why you need to use the INSTEAD OF trigger!

As far as I can see your UPDATE or INSERT i sonly doing what the orioginla SQL which fired the trigger was already doing!
Avatar billede terry Ekspert
27. april 2004 - 19:44 #2
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?
Avatar billede danegreed Nybegynder
27. april 2004 - 23:20 #3
I have to insert something into my tabel, but if the row already exists that row then the row has to be updated instead!
Avatar billede danegreed Nybegynder
27. april 2004 - 23:20 #4
I have to insert something into my tabel, but if the row already exists it has to be updated instead!
Avatar billede terry Ekspert
28. april 2004 - 19:24 #5
?
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!
Avatar billede terry Ekspert
28. april 2004 - 19:25 #6
.
Avatar billede danegreed Nybegynder
29. april 2004 - 13:14 #7
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?
Avatar billede terry Ekspert
29. april 2004 - 19:46 #8
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!
Avatar billede terry Ekspert
04. maj 2004 - 20:51 #9
I think a couple of commenys have gone missing while eksperten ha sbeen down, whats th estatus onthis now?
Avatar billede danegreed Nybegynder
14. maj 2004 - 13:10 #10
I am still looking for a better solution then the one with two calls to the database!
Avatar billede terry Ekspert
16. maj 2004 - 14:03 #11
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.
Avatar billede danegreed Nybegynder
17. maj 2004 - 12:08 #12
ok, i will look into that. Thanks for the effort!!
Avatar billede terry Ekspert
17. maj 2004 - 19:03 #13
selv tak :o)
Avatar billede Ny bruger Nybegynder

Din løsning...

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.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester