For example, I can use a trigger to ensure that any changes to a table are reflected in an Audit log. This can be very usefull in situations where a user/programmer/etc might bypass the application and update a table in the database directly. The trigger can capture who made the update and when, even if the application does not. This makes for a much more secure database.
Trggers can also implement business logic directly into the database so for example, a reorder record can be created for a stock item when a threshold is reached.
Discussion on:
View:
Show:
You can definetly design triggers to cascade updates and deletes, but you may have to worry about your triggers nesting, which the level is 32. You can accomplish the same thing (usually) with cascading referential integrity constraints.
This article forgot to mention that constraints can be used by the optimizer, while TRIGGERs cannot. Constraints are also portable, while TRIGGER code is not very portable. Another old trick to avoid triggers for constraints is to give access to the base table only thru a VIEW that has a WITH CHECK OPTION. This leads to declarative code that the optimizer can use.
>> MyCompany.com recently implemented a business rule that the number of BigScreen products sold in a day can never exceed five if the product is sold for less than $500. The move is an effort to keep discount prices from exceeding the company's wishes. We will look at one of the several ways in which this can be accomplished.
I had to correct the data element names to conform to ISO-11179 rules, but here is a VIEW to enforce that business rule.
CREATE VIEW ConstraintedSalesHistory (sale_ticket, product_type, sale_date, sale_price)
AS
SELECT S1.sale_ticket, S1.product_type, S1.sale_date, S1.sale_price
FROM SalesHistory AS S1
WHERE 5 = ALL (SELECT COUNT(*)
FROM SalesHistory AS S2
WHERE S2.product_type = 'BigScreen'
AND S2.sale_price 500.00
GROUP BY S2.sale_date)
WITH CHECK OPTION;
>> MyCompany.com recently implemented a business rule that the number of BigScreen products sold in a day can never exceed five if the product is sold for less than $500. The move is an effort to keep discount prices from exceeding the company's wishes. We will look at one of the several ways in which this can be accomplished.
I had to correct the data element names to conform to ISO-11179 rules, but here is a VIEW to enforce that business rule.
CREATE VIEW ConstraintedSalesHistory (sale_ticket, product_type, sale_date, sale_price)
AS
SELECT S1.sale_ticket, S1.product_type, S1.sale_date, S1.sale_price
FROM SalesHistory AS S1
WHERE 5 = ALL (SELECT COUNT(*)
FROM SalesHistory AS S2
WHERE S2.product_type = 'BigScreen'
AND S2.sale_price 500.00
GROUP BY S2.sale_date)
WITH CHECK OPTION;
Constraints limit what data can be entered, triggers fire in the process of entering data.
The only time you should use a trigger to block entry is when the schema for one reason or another means you have no choice.
IE the contraint is loose, partially defined or would require an unacceptable level of rework of the existing schema.
Do it simply because you can, leaves you with an inflatable dartboard.
Any requirement to implement a constraint as a trigger is a big red flag in my book.
The only time you should use a trigger to block entry is when the schema for one reason or another means you have no choice.
IE the contraint is loose, partially defined or would require an unacceptable level of rework of the existing schema.
Do it simply because you can, leaves you with an inflatable dartboard.
Any requirement to implement a constraint as a trigger is a big red flag in my book.
Why do the count statements have:
SaleDate >= @Today AND SaleDate > @Today
Isn't that redundant? ">=" is a superset of ">" unless there's something I just don't get about what is happening (always possible!).
SaleDate >= @Today AND SaleDate > @Today
Isn't that redundant? ">=" is a superset of ">" unless there's something I just don't get about what is happening (always possible!).
Sorry about that, it is a typo on my part. It should be:
SaleDate >= @Today AND
SaleDAte @Today + 1
SaleDate >= @Today AND
SaleDAte @Today + 1
What about the performance differences between constraints and triggers used for referential integrity?
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
