SQL Server (και άλλες databases)

Απ: Its a bug || a Feature?

Thiseas — Thu, 31 May 2007 02:19:52 GMT

ok... Επιτρέψτε μου να αναφέρω ένα προβληματάκι που προκύπτει από το παραπάνω.

Έχω το παρακάτω trigger που όταν αλλάζει το ID ή η λιανική τιμή του πίνακα (Products), ενημερώνετε και το ID και η χονδρική τιμή ενός άλλου (Products_Extention).

ALTER TRIGGER Products_TestTrigger ON [dbo].[Products]
FOR UPDATE
AS

if UPDATE(ProductID) or UPDATE(RetailPrice)

begin

UPDATE Products_Extention

SET ProductID = i.ProductID,

WholePrice = i.RetailPrice - (i.RetailPrice * 0.15)

FROM Products_Extention pe

INNER JOIN inserted i ON i.ProductID = pe.ProductID
end

Το παραπάνω trigger δεν θα δουλέψει σωστά διότι το i.ProductID δεν είναι ίσο με το pe.ProductID αν αλλάξει κάποιος το ProductID.
Πώς θα το κάνω να δουλέψει σωστά με το σκεπτικό οτι μπορεί να γίνεται και μαζικό Update των γραμμών του πίνακα Products (πχ. Update Products set ProductID = ProductID+1) γνωρίζωντας οτι δεν θα πέσουμε σε duplicate key γιατί (ας πούμε) οτι τα IDs αυξάνονται κατά 10.

PS: Το ξέρω οτι το παράδειγμα ίσως δεν είναι πρότυπο σωστής ανάλυσης (να αλλάζουν τα IDs σαν τα... "πουκάμισα") ... αλλά ας πούμε οτι είμαστε υποχρεωμένοι να το κάνουμε,...

Απ: Its a bug || a Feature?

Παναγιώτης Καναβός — Thu, 31 May 2007 02:19:52 GMT

Μάλλον δεν έχεις καταλάβει πως δουλεύουν οι triggers και τί περιέχουν οι πίνακες @Inserted και @Deleted. Όταν εκτελείται ένα trigger, ο πίνακας inserted περιέχει όλες τις νέες ή τροποποιημένες γραμμές, με τις νέες τους τιμές. Ο @Deleted περιέχει όλες τις διαγραμμένες ή τροποποιημένες γραμμές, με τις παλιές τους τιμές. Οπότε, για να αλλάξεις τις γραμμές που θέλεις, αρκεί να κάνεις:

update products
set Flag=1
from Products INNER JOIN inserted ON Products.ID=inserted.ID

Και τελείωσες.

Αν θέλεις το UPDATE να εκτελείται μόνο όταν έχει τροποποιηθεί το πεδίο Price, μπορείς να χρησιμοποιήσεις το function UPDATE() για να δεις αν έχει τροποποιηθεί όπως παρακάτω:

IF ( UPDATE(Price))
BEGIN
    update products
    set Flag=1
    from Products
        INNER JOIN inserted
        ON Products.ID=inserted.ID
END

Όλα αυτά μπορείς να τα βρεις στο documentation του SQL Server, καθώς στα βιβλία ή άρθρα που καλύπτουν τα triggers. Οπότε δεν μιλάμε ούτε για bug, ούτε για feature, αλλά για παρανόηση του documentation.

Τέλος, ο τρόπος με τον οποίο χρησιμοποιείς τα inserted και deleted είναι εξαιρετικά αργός. Κάνεις 4 queries για να επιτύχεις ότι θα μπορούσε να γίνει σε 1. Λογικά, είναι το ίδιο να κάνεις ελέγχους αν υπάρχουν τιμές στον inserted και τον deleted και μετά να τραβάς τις τιμές από αυτούς, με το να κάνεις ένα update στο οποίο οι πίνακες θα γίνονται join μεταξύ τους. Ο δεύτερος τρόπος όμως είναι πολύ γρηγορότερος. Ο SQL Server είναι αρκετά έξυπνος για να καταλάβει ότι δεν υπάρχουν εγγραφές, ή ότι δεν υπάρχουν εγγραφές που να ταιριάζουν, πριν προσπαθήσει να κάνει το update. Επιπλέον, η χρήση του COUNT(*) κλειδώνει ολόκληρους τους πίνακες. Αυτό μπορεί να μην είναι μεγάλο πρόβλημα για τους inserted, deleted, θα είναι όμως μεγάλο πρόβλημα αν το εφαρμόσεις σε πίνακες της βάσης και θα οδηγήσει σε σημαντικό blocking και μείωση της ταχύτητας σε καταστάσεις φόρτου.

Απ: Its a bug || a Feature?

KelMan — Thu, 31 May 2007 02:11:41 GMT

Not bug, neither feature

Οι triggers εκτελούνται συνολικά, για όλο το operation και όχι ανά γραμμή. Στο τέλος του operation οι virtual πίνακες Inserted και Deleted περιέχουν όλες τις εγγραφές που "πειράχθηκαν".

Οπότε θα πρέπει να γράψεις κάτι σαν το παρακάτω:

ALTER TRIGGER ProductsTestTrigger ON [dbo].[Products]

FOR UPDATE

UPDATE Products

SET pflag = 1

FROM Products p

INNER JOIN inserted i ON i.id = p.id

Its a bug || a Feature?

Thiseas — Thu, 31 May 2007 01:32:15 GMT

Έχοντας τον παρακάτω πίνακα:

CREATE TABLE [dbo].[Products] (
  [id] [int] IDENTITY (1, 1) NOT NULL ,
  [name] [varchar] (50) COLLATE Greek_CI_AS NULL ,
  [price] [float] NULL ,
  [qty] [float] NULL ,
  [pflag] [int] NULL
) ON [PRIMARY]

Θέλω να φτιάξω ένα trigger το οποίο:
Μόλις αλλάζει καποιος το πεδίο Price να κάνει 1 το πεδίο pFlag:

CREATE TRIGGER ProductsTestTrigger ON [dbo].[Products]
FOR UPDATE
AS

IF ( UPDATE(Price) )
BEGIN
  update products
  set pFlag=1
  from Products
  INNER join inserted on (products.ID = inserted.ID)
END

Εστω οτι εκτελώ το query:

Update Products
set Price = Price * 1.2;

Ποιό είναι το πρόβλημα στο drigger και ενημερώνει (με την σωστή τιμή το πεδίο pflag=1) μόνο μια εγγραφή (την τελευταία)?