SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: An Introduction to Triggers -- Part I
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 04/30/2001 :  01:21:43  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 Posts

Posted - 12/03/2003 :  11:37:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 11/07/2008 :  10:31:08  Show Profile  Reply with Quote
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

Slovenia
11750 Posts

Posted - 11/07/2008 :  10:37:07  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

Greece
1 Posts

Posted - 03/24/2009 :  10:13:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 03/24/2009 :  10:50:53  Show Profile  Reply with Quote
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 Posts

Posted - 03/07/2010 :  11:24:02  Show Profile  Reply with Quote
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 - 03/02/2011 :  15:24:49  Show Profile  Reply with Quote
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

USA
15655 Posts

Posted - 03/02/2011 :  15:51:36  Show Profile  Visit robvolk's Homepage  Reply with Quote
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 - 03/02/2011 :  17:38:49  Show Profile  Reply with Quote
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

USA
15655 Posts

Posted - 03/02/2011 :  18:11:39  Show Profile  Visit robvolk's Homepage  Reply with Quote
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 - 03/02/2011 :  18:43:39  Show Profile  Reply with Quote
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

USA
15655 Posts

Posted - 03/02/2011 :  18:55:26  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000