| 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 |
 |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2008-11-07 : 10:31:08
|
| Good article.I would just change the code to useselect top 1 * for exist clauses instead of select *.Cheers,Valter |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-07 : 10:37:07
|
| why? there's no point in that._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
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 tableso 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 INSERTAS DECLARE @xTarget varchar(255) SELECT @xTarget=INSERTED.Target FROM INSERTED UPDATE LogSummary SET LogSum_Count=LogSum_Count+1 WHERE LogSum_Category=@xTarget;GO |
|
|
|
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.________________________________________________ |
|
|
|
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. |
|
|
|
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') >= 1thanks,jack |
|
|
|
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') |
|
|
|
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 |
|
|
|
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. |
|
|
|
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 |
|
|
|
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. |
|
|
|
|
Article: An Introduction to Triggers -- Part I