Microsoft SQL Server forum - Trying to understand triggers

Author

Trying to understand triggers

2005-07-05, 8:23 pm

Hi,

Newbie here. I have a mailing list program that I really like. I also have a
new membership program. The membership program has mailing list signups
built-in, but it isn't nearly as robust as the stand-alone mail program.

If someone signs up for a newsletter when they register (membership
program), can a "trigger" in SQL Server put the same information into the
stand-alone program's tables? And when they remove or update their
lists/info? And vice-versa?

Is this what a "trigger" would do? And is it efficient?

Thanks,

JA

Erland Sommarskog

2005-07-05, 8:23 pm

JA (jarmour@kc.rr.com) writes:
> Newbie here. I have a mailing list program that I really like. I also
> have a new membership program. The membership program has mailing list
> signups built-in, but it isn't nearly as robust as the stand-alone mail
> program.
>
> If someone signs up for a newsletter when they register (membership
> program), can a "trigger" in SQL Server put the same information into the
> stand-alone program's tables? And when they remove or update their
> lists/info? And vice-versa?
>
> Is this what a "trigger" would do? And is it efficient?

It isn't clear to me whether both these programs use SQL Server databases
or not. If they are, it could possibly be a simple affair. If only the
program that inserts is, it could still be doable, but be less simple and
less efficient.

A trigger is a piece of codes that executes once per *statement*. So
if you insert 20 rows, the trigger fires once, and in the virtual
table "inserted" you have a copy of the 20 inserted rows with that
you can handle just like any other rows. (Well, you can change them.)
For an UPDATE and DELETE statement, you also have a similar table
"deleted" which holds the deleted rows, or before-image in case of an
UPDATE.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

David Portas

2005-07-05, 8:23 pm

The purpose of a trigger is to perform some action when a table is updated
with an INSERT, DELETE or UPDATE statement. Triggers are very powerful and
efficient but need to be used prudently. I've seen all sorts of weird,
complex and burdensome logic hiding away in triggers - stuff that has no
business being inflicted on the poor user every time he wants make an
update. Best keep triggers to fundamental business rules and basic data
manipulation that's essential for every transaction. If you don't need it to
happen within a transaction then it's probably best kept out of a trigger
unless the level of updates is quite small.

You also need to consider the implications for support and future
development. Suppose the DBA needs to update a table to perform an upgrade
or fix some problem. If the trigger is just used to implement an invariable
business rule then no harm is done. However, the more complex and involved
the trigger code the more likely it is to do something that might be
obstructive to fixing some special exceptional case or adding some new
functionality in the future. Selectively disabling a trigger is hard to do
in an operational system. Changing a trigger may mean changing lots of other
code that references that table.

Only you can decide if a trigger is appropriate for your needs but make sure
you study the docs first and understand the merits and demerits of using a
trigger versus alternative methods of copying data and implementing business
rules (procs, views, constraints, scheduled jobs, etc)

--
David Portas
SQL Server MVP
--

Sponsored Links