How to have a trigger call a stored procedure

[phil22046]

The Non-profit I work for sells registrations to courses as the main business. Only once every 24 hours is a process run that updates the counts, and so occasionally there is overbooking. The reason is that it takes a long time to run, and this was a poor design anyway.

I want to add an add trigger to the Registration table to update the counts for that one conference immediately.

So I need that add trigger to take the conference number and pass it to the stored procedure which will use the parameter to decide which conference to update.

I need to have an idea what the top of the stored procedure looks like related to the incoming parameter. And what the trigger has to do to pass the parameter.

 

[phil22046]

This is what I have so far and it will not pass the syntax check.

CREATE TRIGGER [fixcounts] ON [dbo].[REGISTR]
FOR INSERT, UPDATE, DELETE
AS

EXEC ospreytraining2.dbo.UpdateOneConferenceCounts
@The_Cnumber = dbo.registr.cnumber;

 

[PerlyGates]

/*
Firstly its not good practice to be calling the sp from inside the trigger. Secondly, the following assumes that only one row will be updated/inserted. Are you sure this is the case? - otherwise you might have to put in some checking ... I can show you how.

Get rid of "FOR" and replace with AFTER.

Don't know why you've defined the trigger as an INSERT,UPDATE and DELETE - surely just UPDATE will do the trick. If you are looking to handle an INSERT the following will work ( again assuming only one row being inserted- pretty BIG assumption ).

If you do intend handling a DELETE the following will not work ... again I'll show you if needs be.
*/
CREATE TRIGGER [fixcounts] ON [dbo].[REGISTR]
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @my_Cnum int
SELECT @my_Cnum = The_Cnumber
FROM UPDATED

EXEC ospreytraining2.dbo.UpdateOneConferenceCounts @my_Cnum

 

[bborissov]

NEVER,
I mean NEVER (sorry not bigger letters that these) program TRIGGER with assumption that you will update/insery/delete only one record at a time.
remember TRIGGERs are fired at the end of the job and you could have 1,2,3.....10000000..... records that could be updated/inserted/deleted., I am not sure what your SP do but just to update counters there is no need of SP.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLSister]

The problem with PerlyGates solution is that if a multiple record update happens then only the one record from the update will happen. A trigger should never be written to handle one record inserts even if you think that is all that will ever happen. Take it from me, it won't be. There will come a time when someone does a mass update from query Anlayzer and then you lose data integrity.

To repeatedly call a stored proc, you will need to loop through the updated table. This is why it is often a bad idea to call a stored proc from a trigger as this is a slow process if there is a mass update.

"NOTHING is more important in a database than integrity." ESquared

 

[phil22046]

The Cnumber is a 10 character Alphanumric field. The stored procedure will update only one conference.

This is what I have now and it passes the syntax check:

CREATE TRIGGER [fixcounts] ON [dbo].[REGISTR]
AFTER UPDATE

AS

DECLARE @The_Cnumber AS Varchar(10)
Select @The_Cnumber = Registr.cnumber
FROM .dbo.Registr

EXEC ospreytraining2.dbo.UpdateOneConferenceCounts @The_Cnumber

 

[SQLSister]

No, no no. Do not do this. I can't say that strongly enough.

In the first place, you must use the inserted or deleted psuedotables (which conatinthe recrods that are being inserted, delted or changed) or you will be feeding the wrong value to your proc!

And as I said above a very, very, very, very poor practice to write any trigger to handle only one record. You do not control all the ways a table can be updated or inserted! A trigger MUST be able to handle any possibility of how it could be invoked. Any programmer who wrote a trigger that handled only one record where I work would be fired becasue they are compromising the data integrity.

"NOTHING is more important in a database than integrity." ESquared

 

[phil22046]

Ok the motivation for writing this trigger and stored procedure was because we don't have access to the application source code and can only tamper with the database as a way of correcting this problem.

Instead of Update, we could use INSERT, DELETE.

Or we could update all records thru a job under the SQL Agent periodically thru the day and just live with poor performance during this update.

 

[phil22046]

Also the databases are quite small. The largest table has only 40000 records.

 

[SQLSister]

Irrelevant. Size of the database has nothing to do with the importance of the data to the business. And you should learn good programming practices while the database is small so they are second nature by the time you are working on large databases. It is critical to learn how to protect the data integrity of your database. There is nothing more important that data integrity when dealing with databases. As database containing bad data is useless; in fact it can be worse than useless as it can lead to wrong decision making due to giving managers poor information. Do not take the easy way out because you don't want to have to do something more complex or take the time to understand why the more complex process is necessary. Your proposed trigger is a bad thing and will cause harm to your company's data.

"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

CREATE TRIGGER ... FOR INSERT and AFTER INSERT are equivalent. They mean the same thing. I wasn't even aware that the AFTER keyword could be used.

Can't your SP be rewritten to operate on sets instead of one at a time? It could expect a temporary table as its input of work to be done. The temporary table need not be large, it could have simply the PKs of the inserted/updated/deleted rows.

 

[PerlyGates]

I don't think Microsoft would have introduced the AFTER INSERT TRIGGER subsequent to a FOR INSERT TRIGGER without having the intention that people use it ... but I could be wrong!

 

[SQLDenis]

>>FOR INSERT and AFTER INSERT

perhaps you mean INSTEAD OF and AFTER trigger



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

You can request AFTER triggers by specifying either the AFTER or FOR keywords. Because the FOR keyword has the same effect as AFTER, triggers with the FOR keyword are also classified as AFTER triggers.

FOR INSERT and AFTER INSERT are equivalent.

INSTEAD OF triggers fire in place of the triggering action and before constraints are processed.

Who knows what PerlyGates meant. Instead of triggers don't do any data operation at all so they certainly aren't after triggers. They're more like MAYBE-BEFORE triggers, since the data operation is up to the trigger writer. And there are only two kinds of triggers in SQL Server. Oh well.

 

[SQLDenis]

maybe he meant (BE)FOR(E) and AFTER INSERT trigger ;-)

You would use a before trigger if you have to do some operation which you couldn't accomplish with a default

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions