I can do [coding] that is 10 times as complex with triggers as anything
else that you'd even consider.
Why would anyone want trigger code that is ten times more complex than
vanilla SQL that does the same thing?
SQL is a declarative language: you tell the product what you want (e.g.
a constraint) and the product decides how best to implement it. With
triggers you have to manage some of the implementation stuff yourself.
To use our earlier example, which of the following approaches would you
prefer if they were both available in the SQL product of your choice?
Approach 1: CHECK constraint (works in Jet but not in SQL Server):
ALTER TABLE OrgChart ADD
CONSTRAINT can_manage_maximum_three_employees
CHECK (NOT EXISTS (
SELECT T2.manager_employee_ID, COUNT(*)
FROM OrgChart AS T2
GROUP BY T2.manager_employee_ID
HAVING COUNT(*) > 3));
Approach 2: trigger (works in SQL Server but not in Jet):
CREATE TRIGGER can_manage_maximum_three_employees
ON OrgChart
FOR INSERT, UPDATE
AS
IF EXISTS (
SELECT T2.manager_employee_ID, COUNT(*)
FROM OrgChart AS T2
GROUP BY T2.manager_employee_ID
HAVING COUNT(*) > 3
)
BEGIN
RAISERROR ('One or more values are prohibited by the validation
rule ''can_manage_maximum_three_employees'' set for ''OrgChart''.', 16,
1)
ROLLBACK TRANSACTION
END
That's the simplest I can think of and, you are correct, it *did* take
me ten times as long to write than the equivalent Jet CHECK constraint.
Jamie.
--
