Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: An Introduction to Triggers -- Part I

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-04-30 : 01:21:43
This article, submitted by Garth , covers the basics of using triggers. "A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure." If you're a developer and not familiar with triggers this article is a great starting point.

Article Link.

david2004
Starting Member

1 Post

Posted - 2003-12-03 : 11:37:56
Garth,
I find your articles are very useful. I would like to ask for a favor, please email me (dwzhuang@ureach.com) more detail information on the Real-World Example. Let me know how to "log the data to an ODBC-compliant data store"
Thanks in advance.
David
Go to Top of Page

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2008-11-07 : 10:31:08
Good article.

I would just change the code to use

select top 1 * for exist clauses instead of select *.

Cheers,
Valter
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-07 : 10:37:07
why? there's no point in that.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!
Go to Top of Page

azotazot
Starting Member

1 Post

Posted - 2009-03-24 : 10:13:41
i think the way you use the trigger dowsn't make it any faster, since you use the:
SELECT COUNT(*) FROM InetLog WHERE Target = 'AboutUs.htm'
in a trigger, it will still interact with the entire table

so what i suggest is:


change the previous code :
-----------------------------------------
INSERT LogSummary VALUES ('About Us',0)
INSERT LogSummary VALUES ('Services',0)
-----------------------------------------

Into:
-----------------------------------------
INSERT LogSummary VALUES ('AboutUs.htm',0)
INSERT LogSummary VALUES ('Services.htm',0)
-----------------------------------------


and then use the "more smart Trigger ;) "
-----------------------------------------
CREATE TRIGGER tr_InetLog_INSERT
ON InetLog
AFTER INSERT
AS
DECLARE @xTarget varchar(255)
SELECT @xTarget=INSERTED.Target FROM INSERTED
UPDATE LogSummary SET LogSum_Count=LogSum_Count+1 WHERE LogSum_Category=@xTarget;
GO
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-03-24 : 10:50:53
Horrible.
Never write a trigger that can't handle multi-record transactions.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

MrAnonymous
Starting Member

1 Post

Posted - 2010-03-07 : 11:24:02
I would agree with azotazot in that I wouldn't query the InetLog table to get the count every time I wanted to update. Why not just get the count from the records to be inserted and add them to the existing count in the summary? This creates a small chance for inaccuracy if records should somehow manage to get inserted without firing the trigger. But you could have a simple maintenance procedure (executed once in a while through some alternative mechanism) that would grab the full count from the log table and update the summary.
Go to Top of Page

jacksql
Starting Member

3 Posts

Posted - 2011-03-02 : 15:24:49
for a multi insert trigger would the following work:

IF (SELECT COUNT(*) FROM inserted WHERE Ord_Priority = 'High') >= 1

thanks,
jack
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-02 : 15:51:36
It works but is inefficient, this is better:

IF EXISTS(SELECT * FROM inserted WHERE Ord_Priority = 'High')
Go to Top of Page

jacksql
Starting Member

3 Posts

Posted - 2011-03-02 : 17:38:49
thanks robvolk.

I will use if exists.

I have an issue with insert trigger. I created a FOR INSERT trigger on a table to email whenever condition is met. I tested this by manually inserting single and multiple records and it works fine. However the client application is not able to insert any records with the trigger in place. any idea why?

thanks,
jack
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-02 : 18:11:39
Besides permissions issues, I can't say what the problem might be. I CAN say this: you DO NOT want to send email from a trigger, ever. Set up a queue table to insert messages into, and have a scheduled job process and clear that table. A trigger should do the absolute minimum necessary work, complete as quickly as possible, and not access anything external to the server. Even Database Mail would introduce an unnecessary lag.

If you need instant delivery of email, look at Service Broker. You can push a message onto a queue and have an activation procedure send the email. The benefit is that this happens asynchronously and doesn't impact the trigger.
Go to Top of Page

jacksql
Starting Member

3 Posts

Posted - 2011-03-02 : 18:43:39
Dear robvolk,

thank you very much for you quick reply. You are right, it was a permissions problem. I was using Database Mail to email which uses the msdb sys db which the application sqluser did not have permissions to.

hmm... interesting point you bring up about not emailing in a trigger, i don't know how to use Service Broker i would have to do some reading on it, what if I convert the trigger to a stored procedure as the article shows?

thanks,
jack
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-02 : 18:55:26
Only if the stored procedure replaces the function of the trigger. If you're talking about calling the procedure FROM the trigger, then it's the same problem I described earlier.

Triggers extend/continue the transaction context of the INSERT/UPDATE/DELETE statements that fire them, so any locks that were taken are held until the trigger completes. That's why you don't want a potentially long-running process to run in a trigger, because it will block the table until it commits or rolls back.
Go to Top of Page
   

- Advertisement -