Good Trigger Examples?

[davej]

Where can I find some good examples of SQL Server triggers? My textbook uses the lame example of a department that can't be deleted by the last employee in that department. Also I don't understand why you would choose INSTEAD OF vs. AFTER when they can both be rolled back.Thanks!

[boen_robot]

There are examples in MySQL's documentation on triggers. The one given there is of an "accumulator" that apparently adds up a value to a variable at each row insertion.... hmm... I don't know if that's useful in any real scenario.Well, here's one more real for you - imagine that instead of integers being the primary auto-incrementing key, you have something else to serve as an ID to a record... say, the social security number of a person. Suppose that there is an algoritm for generating new SSNs for new borns that are to be inserted into the DB (we're assuming you're creating a database for a government institution that has the right to generate actual valid SSNs of course). Now, you could implement this in the program layer, or you can do it on the database server with triggers. Doing it with triggers would ensure that no one will accidently create the same SSN. So, from the program layer, you just enter the details of the new born (name, mother SSN, father SSN), and since you've previously set a trigger, the new born's SSN is automatically generated.