Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







SQL Email Trigger


Hi,
I am looking for a SQL trigger that will send email notification to a mailing list whenever a folders contents have been changed.
Can anyone help on this as I have little to no experience with SQL??


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Email Trigger
Hello
Can you setup a trigger to mail some one when a record is updated in SQL server 2005?
If you can, can anyone help me?
C

View Replies !   View Related
Can A Job Trigger Itself Upon Receipt Of An Email?
Hi all,

I have a SQL Server Job that I would like to run when I receive an email saying "Data is now ready to be imported to SQL Server" ?

Is there a way to accomplish this.

- Vivek

View Replies !   View Related
SQL Trigger And Auto Email
Hi,

I have a trigger for a table which stores email information generated from an ACCESS form. The trigger should send an auto email response to users who submitted an email to request for their password (we have forgetful users!). There is something wrong with my trigger because the auto email is sent out with a blank body... I will appreciate any advice!

Thank you!

My trigger:
CREATE TRIGGER tr_SendPassword ON PIPEmail
FOR INSERT
AS
DECLARE @Password varchar(100)
DECLARE @EmailAddress varchar(100)
DECLARE @message varchar(100)
IF (select count(*) from inserted) = 1
BEGIN
IF exists (SELECT * FROM inserted
WHERE Subject = 'Forgot my password')
BEGIN
select @Password = 'We received an email request from you for your password. Your password for SAR Search is: ' + UserRole.Password,
@EmailAddress = [User].Email
from UserRole
join inserted on UserRole.WindowsUser = inserted.WindowsUser
join [User] on [User].WindowsUser = UserRole.WindowsUser

exec master.dbo.xp_sendmail @recipients=@EmailAddress,
@subject='SAR Search password request',
@message=@Password
END
END

View Replies !   View Related
Email Trigger In SQL 2005
I am new to developing as will be evident from this post. Your help will be greatly appreciated.

I am developing an intranet for our company using ASP.NET with a SQL backend. I am currently working on a suggestion box form.

I would like to have an email sent to specific persons when a new entry is made in the suggestion table. I have been able to configure the trigger and generate the email (This was easy). Formatting the email has proven more difficult to resolve.

The format I would like is somewhat as follows:

F_NAME L_NAME submitted the following suggestion:

IDEA

BENEFIT

APPROVE | DECLINE

The items in RED are columns in the table and the Blue Underlines are hyperlinks to change the Status column in the table.

How can I generate the email to contain the data from the inserted record and in the above format.

Being new at this I only now how to send a static email advising that the entry has been made.

Any help creating the dynamic email form for this trigger will be greatly appreciated.

Lastly, what books are most helpful for SQL, ASP.NET, and VBScript referencing and examples?

Thanks

View Replies !   View Related
Create A Trigger To Send Email
I have a basic trigger that populated an audit table, but I want to add logic to that to send an email everytime the trigger is called,Is there a easy way to add code to my basic trigger to send an email to me everytime the data changes.Thanks

View Replies !   View Related
Creating A Trigger That Sends An Email
Can anyone assist me?
I am trying to create a trigger within our local database that will allow me submit an email to a local exchange server. Its an internal email that our company has created for responses by candidates when the original stored procedure meets a condition of TRUE.

Is SQL 7.0 capable of sending emails to a specified address through a Trigger? I was told it could but have yet to come across any documentation backing this up.

Would anyone know the generic syntax I can use to create such a trigger?

Any suggestions would be appreciated.

Thanks in advance,
Claude Johnson
cjohnson@staffmentor.net

View Replies !   View Related
Date Driven Email Trigger?
Hello to a new forum. I am very impressed with the level of ability I have been seeing on this forum and all the helpful posts that are going on.

I am hoping someone can help me solve my problem. I know enough SQL to be very dangerous how to create, delete, insert, update etc... and how to build queries. Here is my dilemma I need to have a piece of scheduling software send out reminder emails based on a DATETIME field. I am running SQL Server 2005, and the database mail is already configured and can successfully send out email from the management studio interface.

Does anyone know of a tutorial or can point me in the right direction to accomplish this task. I am well versed in PHP, and am hoping I might be able to get the server to trigger a PHP script or something to this extent.

View Replies !   View Related
Try To Make A Clr Trigger To Send Email
Hello,
 
I'm tryng to set up a CLR Trigger, when executing i get this error:
 
Msg 6522, Niveau 16, État 1, ProcĂ©dure Trigger_EnvoiEmail, Ligne 1
Une erreur .NET Framework s'est produite au cours de l'exĂ©cution de la routine ou de la fonction d'agrĂ©gation dĂ©finie par l'utilisateur 'Trigger_EnvoiEmail' :
System.Security.SecurityException: Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
   at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
   at System.Security.CodeAccessPermission.Demand()
   at System.Net.Mail.SmtpClient.Initialize()
   at System.Net.Mail.SmtpClient..ctor(String host)
   at AffYEmailQueue.MailHelper.SendMailMessage(String from, String destinataire, String bcc, String cc, String subject, String Htmlbody, String Textbody, Int32 idAFFY)
   at AffYEmailQueue.Triggers.EnvoiEmail()

 
Can anybody help me ? thanks

View Replies !   View Related
Need Help With Error In Email Alert Trigger
Hi All,

I'm not a SQL programmer by any means, this is my first attempt at creating my own trigger and I'm hacking up an existing trigger that performed a different function.

In a nutshell, we use a business app for inputting sales orders. When an order taker leaves out the commission cost on an order I want them to receive an email letting them know.

So when my p21.dbo.oe_line.commission_cost has a value of 0 I want to send the mail

I hacked up another script that sent out an email alert but I'm kinda stabbing in the dark here and fixing my errrors with a little testing and a lot of googling. I've found a new one that I don't exactly understand.

Msg 4104, Level 16, State 1, Procedure Zero_Commission_Cost_Notification, Line 31The multi-part identifier "dbo.oe_line.commission_cost" could not be bound.

 I'm rather impressed with myself that I was able to get all the way to line 31 of a 40 line TSQL without error but now I'm stuck. Here is my trigger - does anyone see anything blatantly wrong with how it's setup? Any/all help is GREATLY appreciated!!


set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoCREATE TRIGGER [Zero_Commission_Cost_Notification] ON [dbo].[oe_hdr_salesrep]WITH EXECUTE AS SELF FOR INSERT, UPDATEAS DECLARE            @OrderNo as varchar(8),            @CustomerID as varchar(19),            @TakerID as varchar(15),            @TakerEmail as varchar(40),            @Recipients as varchar(50),            @Subject as varchar(80),            @BillToName as varchar(80) SELECT @OrderNo = 'order_no',            @CustomerID = 'customer_id'            FROM insertedSELECT @TakerID = 'taker'            FROM oe_hdr            WHERE order_no = @OrderNoSELECT @TakerEmail = 'email_address'            FROM users            WHERE id = @TakerIDSELECT @BillToName = 'bill2_name'            FROM invoice_hdr            WHERE customer_id = @CustomerID            SELECT @Subject = 'Order #' + @OrderNo + ' for ' + @BillToName + ' has a $0 commission cost, please fix'IF dbo.oe_line.commission_cost = 0            RETURN            IF @TakerEmail = '' or @TakerEmail is null                       BEGIN                                    EXEC msdb.dbo.sp_send_dbmail                                                @profile_name = 'Alerts',                                                @Recipients = @TakerEmail,                                                @Subject = @Subject                                    END

View Replies !   View Related
Insert Trigger Not Getting Row Data For Email Body
hello,
need help with a simple trigger i have been working on. the trigger automatically sends me an email out when a record is inserted, how ever i can't seem to get the row column data into the email. The part i do not understand is that I get the row column data  information in the email if I update the row.
This is for 2005 SQL
Any direction would be greatly appreaciated
OneIDesigned
 

View Replies !   View Related
SP Or Trigger For Email Of New Record Insert To Table
Does anyone have a stored procedure for sending an email (using SQLMail) when a new record is added to a database table?

I have SQLMail all setup, I just need a good example of a stored procedure or trigger that will sending an email with the details for a new record entry into a table.

thanks!

View Replies !   View Related
Sending A Report Via Email Triggered From A Trigger On A Sql Table
 
My question is it possible to send a report developed in reporting services via email as either a attachment or imbedded in the email, from an insert trigger placed on a table in sql.
If so could you please help with the string that would be need to achieve this.
The report will need to have one or two parameters passed to it from the table.
The report is built and working through reporting services already. I was impressed with the subscription services that reporting services has in place and would like to utilise the reports further by auto emailing out when a new record is placed in the table. To be sent out either as an attachment or imbedded in the email it self.
Thanks for any assistance in advance
David

View Replies !   View Related
SQL Trigger - Email Data Based On Date Field
I need to send an email when my 'LastRunDate' field is 30 days old (i.e. It should send an email if the LastRunField = 7/21/2007).

I would need to include the matching fields in the database (i.e. MachineID, Description, etc.) then update that field to todays date.

I have a few values in the 'Frequency' field such as Daily, Monthly, Yearly. Daily would be 24 hrs, monthly 30 days, yearly 365 days from the lastrundate.

I am new to T-SQL & need a good p[lace to start.

Any sugesstions.

Thanks.

View Replies !   View Related
Dbmail Doesn't Rely On IIS SMTP, How To Set Bounced Email Redirect Email Etc.? Thanks
Under IIS SMTP I can set bounced email redirect etc. how to do that with dbmail, the idea is I can get the list of bounced emails somewhere so I can create a report.

 

Any idea?

 

thanks

View Replies !   View Related
Mass Email: Text Body Keeps Looping In Each Email
First off, my server uses CDO, the updated version of CDONTS, to send mail.

The script works however, it sends the emails out and keeps looping the message body within the text of the email. here's an example:

I added fake users to my table called REALTORS.

this was the result of the email sent out:

<!-- START EMAIL MAIL OUT -->
I am an realtor in Florida and I found you from surfing around

Poop Title
http://www.poop.com

I have a real estate site and I am interested in exchanging links with your site. This is benefitial to you and I because each external website that links to your page counts as a "vote" for your website. The more links to your site, the better search engine placement you will recieve in google (it's called Google PageRank)...which ultimately means more exposure. It's a win/win situation.

If you are interested, you may add your website to my link directory at the address below (it's all automated). Make sure to pick your correct category and sub-categories: http://www.realestate321.com/resources/resources.asp
Once added you will be placed in a "holding bin" waiting to be approved. Your site will be approved when I find a link on your website to my website. My website will send out a "robot" once a month to make sure that my link is still on your site. If it's not understandably, your link will be removed from my page by that robot.

Best Regards,

Nick McGirr
Real Estate Agent
http://www.realestate321.com/
"Buying and Selling Homes in the (321) Area Code."I am an realtor in Florida and I found you from surfing around

Haha titleage
http://www.hahaha.com

I have a real estate site and I am interested in exchanging links with your site. This is benefitial to you and I because each external website that links to your page counts as a "vote" for your website. The more links to your site, the better search engine placement you will recieve in google (it's called Google PageRank)...which ultimately means more exposure. It's a win/win situation.

If you are interested, you may add your website to my link directory at the address below (it's all automated). Make sure to pick your correct category and sub-categories: http://www.realestate321.com/resources/resources.asp
Once added you will be placed in a "holding bin" waiting to be approved. Your site will be approved when I find a link on your website to my website. My website will send out a "robot" once a month to make sure that my link is still on your site. If it's not understandably, your link will be removed from my page by that robot.

Best Regards,

Nick McGirr
Real Estate Agent
http://www.realestate321.com/
"Buying and Selling Homes in the (321) Area Code."I am an realtor in Florida and I found you from surfing around

niclipse . com
http://www.niclipse.com

I have a real estate site and I am interested in exchanging links with your site. This is benefitial to you and I because each external website that links to your page counts as a "vote" for your website. The more links to your site, the better search engine placement you will recieve in google (it's called Google PageRank)...which ultimately means more exposure. It's a win/win situation.

If you are interested, you may add your website to my link directory at the address below (it's all automated). Make sure to pick your correct category and sub-categories: http://www.realestate321.com/resources/resources.asp
Once added you will be placed in a "holding bin" waiting to be approved. Your site will be approved when I find a link on your website to my website. My website will send out a "robot" once a month to make sure that my link is still on your site. If it's not understandably, your link will be removed from my page by that robot.

Best Regards,

Nick McGirr
Real Estate Agent
http://www.realestate321.com/
"Buying and Selling Homes in the (321) Area Code."I am an realtor in Florida and I found you from surfing around.

I have a real estate site and I am interested in exchanging links with your site. This is benefitial to you and I because each external website that links to your page counts as a "vote" for your website. The more links to your site, the better search engine placement you will recieve in google (it's called Google PageRank)...which ultimately means more exposure. It's a win/win situation.

If you are interested, you may add your website to my link directory at the address below (it's all automated). Make sure to pick your correct category and sub-categories: http://www.realestate321.com/resources/resources.asp
Once added you will be placed in a "holding bin" waiting to be approved. Your site will be approved when I find a link on your website to my website. My website will send out a "robot" once a month to make sure that my link is still on your site. If it's not understandably, your link will be removed from my page by that robot.

Best Regards,

Nick McGirr
Real Estate Agent
http://www.realestate321.com/
"Buying and Selling Homes in the (321) Area Code."

<!--[==-- EMAIL MAIL OUT FINISHED ------->


ANY IDEAS ON HOW TO SEND EACH EMAIL TO EACH USER INDIVIDUALLY WHILE USING THE INFORMATION LINKED IN THE DATABASE WITHOUT LOOPING OTHER PEOPLE'S DB INFO IN THE SAME EMAIL?


Here's the script below....


<title>Email The List</title>

<%
nickemail = request.form("sender")
subject = request.form("subject")


If Len(subject) > 0 Then

dsn="Provider=SQLOLEDB;Data Source=sql.string.com, etc ect"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open dsn

Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT title, url, email FROM Realtors"

RS.open SQL, conn


Do While NOT RS.eof

strTXT = strTXT & "I am an realtor in Florida and I found you from surfing around"

If Len(RS("title")) > 0 Then
strTXT = strTXT & vbcrlf & vbcrlf & RS("title") & vbcrlf
strTXT = strTXT & RS("url") & vbcrlf & vbcrlf

Else
strTXT = strTXT & "." & vbcrlf & vbcrlf
End If

strTXT = strTXT & "I have a real estate site and I am interested in exchanging links with your site. This is benefitial to you and I because each external website that links to your page counts as a ""vote"" for your website. The more links to your site, the better search engine placement you will recieve in google (it's called Google PageRank)...which ultimately means more exposure. It's a win/win situation." &vbcrlf &vbcrlf
strTXT = strTXT & "If you are interested, you may add your website to my link directory at the address below (it's all automated). Make sure to pick your correct category and sub-categories:" &vbcrlf
strTXT = strTXT & "http://www.realestate321.com/resources/resources.asp" & vbcrlf
strTXT = strTXT & "Once added you will be placed in a ""holding bin"" waiting to be approved. Your site will be approved when I find a link on your website to my website. My website will send out a ""robot"" once a month to make sure that my link is still on your site. If it's not understandably, your link will be removed from my page by that robot." & vbcrlf &vbcrlf

strTXT = strTXT & "Best Regards," & vbcrlf & vbcrlf
strTXT = strTXT & "Nick McGirr" & vbcrlf
strTXT = strTXT & "Real Estate Agent" & vbcrlf
strTXT = strTXT & "http://www.realestate321.com/" & vbcrlf
strTXT = strTXT & """Buying and Selling Homes in the (321) Area Code."""

set objMessage = createobject("cdo.message")
set objConfig = createobject("cdo.configuration")

' Setting the SMTP Server
Set Flds = objConfig.Fields
Flds.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
Flds.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "localhost"
Flds.update

Set objMessage.Configuration = objConfig

objMessage.To = RS("email")
objMessage.From = nickemail
objMessage.Subject = subject

objMessage.TextBody = strTXT
'objMessage.HtmlBody = strHTML
objMessage.fields.update
objMessage.Send

RS.movenext

Loop


Conn.close
set Conn = nothing



set objMessage = nothing
set objConfig = nothing


ShowThanksMsg
Else

End If
ShowForm



Sub ShowForm
%>

<%
Session("department")="home"
%>


<center>
<form action=email.asp method=post>
<table border=0 cellpadding=4 cellspacing=2>

<table>
<tr><td><font class=tbody><b>From:</b></font></td><td><input type=text name=sender value="me@me.com"></td></tr>
<tr><td><font class=tbody><b>To:</b></font></td><td><font class=tbody>Brevard County Emails</font></td></tr>
<tr><td><font class=tbody><b>Subject:</b></font></td><td><input class=inputfield type=text name=subject size=22></td></tr>
<tr><td colspan=2><input type=image align=right src=/_images/submit.gif border=0 name=B1></td></tr>
</table>
</form></center>

<!-- <tr><td colspan=2><font class=tbody><b>Comments:</b></font><br><textarea class=inputfield rows=3 maxlength=100 name=message cols=40></textarea></td></tr> -->

<%
End Sub

Sub ShowThanksMsg()
response.write "<center><font size=5><b>Message Sent!</b></font><br><br>"
response.write "your message has been delivered to all members."

End Sub
%>

View Replies !   View Related
Help Split List Of Email Add Comma For Evry Email
need help
split list of email add comma for evry email
i have tabe "tblLogin" and in this table i have field emall
like this
 
emall    
-----------------------------------------
aaa@hhhh.mm
nnn@hhhh.mm
mmm@hhhh.mm
 
need to do  ilke this



Code Snippet
@list_email = (SELECT     emall  FROM         tblLogin)
 
--------------------------i get this
-----------------------@list_email=aaa@hhhh.mm ; nnn@hhhh.mm ; mmm@hhhh.mm
 
@recipients = @list_email
 
 


 
 
 



Code Snippet

IF EXISTS( SELECT * FROM [db_all].[dbo].[taliB] )

 

BEGIN

DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)

SET @xml =CAST(( SELECT

FirstName AS 'td','',

LastName AS 'td','' ,

Date_born AS 'td','' ,

Age AS 'td','' ,

BirthdayToday AS 'td','' ,

BirthdayThisWeek AS 'td'

FROM [Bakra_all].[dbo].[taliB] ORDER BY LastName FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><H1 align=center>aaaaaaaaaaaaaaaaaaaaaa</H1><body ><table border = 1 align=center dir=rtl>

<tr>

<td>name</td>

<td>fname</td>

<td>date</td>

<td>age</td>

<td>aaaaaaaaa</td>

<td>bbbbbbbbbbbbbbb</td>

</tr>'

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@recipients =N'rrr@iec.co.il',

@copy_recipients='rrrrr@iec.co.il',

@body = @body,

@body_format ='HTML',

@subject ='ggggggggggggggggggggg',

@profile_name ='ilan'

END

ELSE

print 'no email today'
 

View Replies !   View Related
Load A Text File With Email Addresses And Compare Against A Database Table That Has Email Addresses And User_id
Hello ALL

 

what I want to achieve is to load a text file that has email addreses from disk and using the email addresses in the text file look it up against the email addresses in the database table then once matched delete all the users in the table whose email address were in the text file.

 

I also want to update some users using a different text file.

 

Please help me with the best way to do this

 

Thanks in advance

 

View Replies !   View Related
Trouble With Update Trigger Modifying Table Which Fired Trigger
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'

View Replies !   View Related
CLR-Based Trigger? Recursive Trigger? Common Table Expression?
Hey,

I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.

A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.

A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.

Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.

If you need the design or create script (table layout), please let me know.

Thanks.

View Replies !   View Related
Generic Audit Trigger CLR C#(Works When The Trigger Is Attached To Any Table)
This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work.  Be sure and create the 'Audit' table first though.

The following code write audit entries to a Table called
'Audit'
with columns
'ActionType'    //varchar
'TableName'    //varchar
'PK'    //varchar
'FieldName'    //varchar
'OldValue'    //varchar
'NewValue'    //varchar
'ChangeDateTime'    //datetime
'ChangeBy'    //varchar

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
//A Generic Trigger for Insert, Update and Delete Actions on any Table
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]

public static void AuditTrigger()
{
SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context
string TName; //Where we store the Altered Table's Name
string User; //Where we will store the Database Username
DataRow iRow; //DataRow to hold the inserted values
DataRow dRow; //DataRow to how the deleted/overwritten values
DataRow aRow; //Audit DataRow to build our Audit entry with
string PKString; //Will temporarily store the Primary Key Column Names and Values here
using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection
{
conn.Open();//Open the Connection
//Build the AuditAdapter and Mathcing Table
SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn);
DataTable AuditTable = new DataTable();
AuditAdapter.FillSchema(AuditTable, SchemaType.Source);
SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us
//Get the inserted values
SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);
DataTable inserted = new DataTable();
Loader.Fill(inserted);
//Get the deleted and/or overwritten values
Loader.SelectCommand.CommandText = "SELECT * from DELETED";
DataTable deleted = new DataTable();
Loader.Fill(deleted);
//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM
ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn);
TName = cmd.ExecuteScalar().ToString();
//Retrieve the UserName of the current Database User
SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);
User = curUserCommand.ExecuteScalar().ToString();
//Adapted the following command from a T-SQL audit trigger by Nigel Rivett
//http://www.nigelrivett.net/AuditTrailTrigger.html
SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '" + TName + @"'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);
DataTable PKTable = new DataTable();
PKTableAdapter.Fill(PKTable);
 
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table
{
case TriggerAction.Update:
iRow = inserted.Rows[0];//Get the inserted values in row form
dRow = deleted.Rows[0];//Get the overwritten values in row form
PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string
foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns
{
if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "U";//U for Update
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry
}
}
break;
case TriggerAction.Insert:
iRow = inserted.Rows[0];
PKString = PKStringBuilder(PKTable, iRow);
foreach (DataColumn column in inserted.Columns)
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "I";//I for Insert
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = null;
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
case TriggerAction.Delete:
dRow = deleted.Rows[0];
PKString = PKStringBuilder(PKTable, dRow);
foreach (DataColumn column in inserted.Columns)
{
//Build and Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "D";//D for Delete
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = null;
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
default:
//Do Nothing
break;
}
AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable
conn.Close(); //Close the Connection
}
}
 
 
//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values
//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"
public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)
{
string temp = String.Empty;
foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed
{
temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,"));
}
return temp;
}
}

The trick was getting the Table Name and the Primary Key Columns.
I hope this code is found useful.

Comments and Suggestion will be much appreciated.

View Replies !   View Related
Unable To Get Application That Intiated The Table Trigger Within A CLR Trigger
Inside a clr DML trigger SELECT APP_NAME() does not return the application which initiated the trigger.

View Replies !   View Related
Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table
A





ID

Name


1

Joe


2

Fred


3

Ian


4

Bill
 
 
B





ID


1


4
 
I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B.  Is it possible to find out the login id of the user inserting a row?
 
I believe the trigger should look something like this:
 
create trigger test_trigger
on a
for insert
as
insert into b(ID)
 
select i.id
from inserted i
where
--specific USER

View Replies !   View Related
Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table
Table 1
 




First_Name

Middle_Name

Surname


John

Ian

Lennon


Mike

Buffalo

Tyson


Tom

Finney

Jones
 
Table 2




ID

F

M

S

DOB


1

Athony

Harold

Wilson

24/4/67


2

Margaret

Betty

Thathcer

1/1/1808


3

John

Ian

Lennon

2/2/1979


4

Mike

Buffalo

Tyson

3/4/04


5

Tom

Finney

Jones

1/1/2000
 
 
I want to be able to create a trigger that updates table 2 when a row is inserted into table 1.  However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.
 
 

View Replies !   View Related
Trigger UPDATEing Another Table With An UPDATE Trigger...
Hi,
Let's say I have Table A & B. I have a trigger for UPDATE and INSERT on table A that updates a field in table B. This works great. I have just realized that I need to keep the field in table B from being updated by anything other than table A's trigger. Still with me... So I added a trigger to table B for INSERT and UPDATE that looks like the following:

IF UPDATE(Quantity)
BEGIN
RAISERROR(50001, 16, -1)
ROLLBACK TRAN
END

The user can change anything in table B except the Quantity field. Is there a way to disable the trigger on table B from firing when the UPDATE is fired from the trigger on table A?

-Alan

View Replies !   View Related
How To Create New CLR Trigger From Existing T-Sql Trigger
how to create new CLR trigger from existing T-Sql Trigger Thanks  in advance

View Replies !   View Related
Modifing The Row That Invokes A Trigger From Within That Trigger
When a row gets modified and it invokes a trigger, we would like to beable to update the row that was modified inside the trigger. This is(basically) how we are doing it now:CREATE TRIGGER trTBL ON TBLFOR UPDATE, INSERT, DELETEasupdate TBLset fld = 'value'from inserted, TBLwhere inserted.id= TBL.id....This work fine but it seems like it could be optimized. Clearly we arehaving to scan the entire table again to update the row. But shouldn'tthe trigger already know which row invoked it. Do we have to scan thetable again for this row or is their some syntax that allows us toupdate the row that invoked the trigger. If not, why. It seems likethis would be a fairly common task. Thanks.

View Replies !   View Related
Disabilitazione Trigger [DISABLE TRIGGER]
Salve, non riesco a disabilitare un trigger su sqlserver nč da queryanalyzer, nč da enterprise manager.In pratica tal cosa riuscivo a farla in Oracle con TOAD, mentre qui nonriesco.Mi interessa disattivarlo senza cancellarlo per poi riattivarlo al bisognosenza rilanciare lo script di creazione.Grazie a tuttiHi I need to disable a DB trigger and I'm not able to do this neither withquery analyzer, neither with enterprise manager.I remeber this job was quite simple using TOAd in Oracle.I'm interested in making it disabled not delete it, without run creationscript.Thanks a lot to everybody.

View Replies !   View Related
Update Trigger Behaviour W/o A Trigger.
Hi,
I am not sure if this is the right forum to post this question.
I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio.
When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H".
This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table.
What else would cause the database automatically change my update?
Could there be any other place I should look for an update trigger on this table?
Thanks,
 

View Replies !   View Related
Drop Trigger With A Variable Trigger Name
 

Hi all in .net I've created an application that allows creation of triggers, i also want to allow the deletion of triggers.
The trigger name is kept in a table, and apon deleting the record i want to use the field name to delete the trigger
 
I have the following Trigger
 
the error is at

DROP TRIGGER @DeleteTrigger
 
I'm guessing it dosen't like the trigger name being a variable instead of a static name
how do i get around this?
 
thanks in advance

-- ================================================

-- Template generated from Template Explorer using:

-- Create Trigger (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- See additional Create Trigger templates for more

-- examples of different Trigger statements.

--

-- This block of comments will not be included in

-- the definition of the function.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE TRIGGER RemoveTriggers

ON tblTriggers

AFTER DELETE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

Declare @DeleteTrigger as nvarchar(max)

select @DeleteTrigger = TableName FROM DELETED

 

IF OBJECT_ID (@DeleteTrigger,'TR') IS NOT NULL

DROP TRIGGER @DeleteTrigger

GO

END

GO

View Replies !   View Related
Trigger Doesn't Log Into The Audit Table If The Column Of Table Has Trigger On Is Null
 

Hi,
 
I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT  log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out

and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up
 
Any thoughts?
 
 
Here is my t-sql
 

Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)

select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d

where i.req_guid = d.req_guid

and i.req_fname <> d.req_fname

 
 
Thanks,
leo

View Replies !   View Related
Email
Quick question ..does DatabaseMail work in sql server express 2008.And if not ... is there a simple work around / solution to get thesame effect .. ie to send email out from sql expressRegardsRob

View Replies !   View Related
SQL Email
I want to write a stored procedure that will email Sales Associates and notify them if a customer has had a return number issed for return of defective product. I wanted to do this daily. I am using SQL Server 2000. Is there a way to get SQL to send an email without using Microsoft Exchange? I have a network that is using Workgroup mode and not domains. I can provide SQL a mailbox on our Linux mail server to send the mail.

View Replies !   View Related
Email
Hi all me again I gotta question, the Sr executive asst has a number of reports that gets emailed to different department, only problem is those departments are on a totally different server. I am guessing that we need to start SQL AGENT Emailing MAPI to do this for us, of course I could be wrong. This is one of the stored procedures that creates one of the reports

CREATE PROCEDURE createSecurityListtobeEmailed
AS
SELECT SecurityListX."Last Name", SecurityListX."First Name", SecurityListX.MI, SecurityListX.Sex, SecurityListX.DOB, SecurityListX.IR#, SecurityListX."Issue Date", SecurityListX."Date served", SecurityListX.Duration, SecurityListX."Exp Date", SecurityListX."Reason for Exclusion", SecurityListX."Added Comment", SecurityListX.type INTO SecurityList
FROM SecurityListX
GROUP BY SecurityListX."Last Name", SecurityListX."First Name", SecurityListX.MI, SecurityListX.Sex, SecurityListX.DOB, SecurityListX.IR#, SecurityListX."Issue Date", SecurityListX."Date served", SecurityListX.Duration, SecurityListX."Exp Date", SecurityListX."Reason for Exclusion", SecurityListX."Added Comment", SecurityListX.type
HAVING (((SecurityListX."Last Name") Is Not Null))
GO

View Replies !   View Related
Email Through SQL
I am trying to setup my sql to send automatic emails to different companies but I am not doing so well. The user puts an email address in the database and then presses a button (command button) that will execute a stored procedure xp_sendmail to a recepient. I need to know how to include read receipts and delivery receipts into the stored procedure, because right now its set up in his outlook as a rule to do this and I want to set it up in sql. The user deals with companies and these companies have several contacts with several email addresses which sql will store in a seperate table.

View Replies !   View Related
Email
How can I implement the functionality of sending mail with the help of SP or DTS?
Subhasish

View Replies !   View Related
Sql Email
I have created a dts email task to attach an externally created file.
Outlook is installed on the local server which we use to process email. The problem is that email messages appear in the sent items folder in outlook as unsent items (Envelope Closed icon) If i manually forward the email under outlook everything works fine.

Any Ideas

Sql 2000 sp4
Outlook 2000

Thanks in advance

Mike Osbourn

View Replies !   View Related
Email From A Job
Trying to get a job step to send an email with an explaination in certain failure conditions. The problem is I want the recipient to be the job's operator.
don't know of a way to accesss the job's operator email address cleanly from inside a step

Any ideas?

View Replies !   View Related
Email
HI,
I have sucessfully setup email on SQL 2000 using Exchange. It works fine with
xp_sendmail but when I test the email account on operator it tells me that
the mail was sent successfully but I never get the email?

Help??

Thanks!!

View Replies !   View Related
DTS- Email How TO
IF I HAVE ONE OR MANY PACKAGES HOW DO SEND AN EMAIL TO MY BOSS IF IT BLOWS UP?? ex. INPUT IS MY TRUNCATE STEP, CONNECTION 1 IS MY ODBC, CONECTION 2 IS WHERE THE RESULT WILL BE PLACED INTO. AT ANY ONE OF THOSE STEPS, It BLOWS HOW TO SEND AN EMAIL. THE EMAIL IS ALREADY SETUP BECAUSE I TESTED IT IN THE QUERY ANALYZER WITH THIS STATEMENT ( EXEC xp_sendmail @recipients = 'robertk;laurac',
@message = 'The master database is full.',
@copy_recipients = 'anned;michaels',
@subject = 'Master Database Status') AND IT WORKS.

View Replies !   View Related
Email From SQL
How do i get the email to be send automatically from SQL

My SQL Server is configured so if sends an email after running a job. The email is been succesfully sent by SQL, but is not atually sent until you login and start up Microsoft Outlook and client on Send and Receive.

Surely there is a way to get SQL to automatically send the message.

I have got outlook to send message immeditally.

My mail server is SendMail - SCO Open Server 5.06

Thanks

David

View Replies !   View Related
SQL Email
I've setting up SQL email for alerts and I've got Outlook Express working
with our SMTP server. But when I try testing email to an operator, I'm getting a
"Error 22022: SqlServerAgen Error: The SQLServerAgent mail session is not
running, check the mail profile and/or the SQLServerAgent service startup
account in the SQLServerAgent Properties dialog."

I checked the archives of this list and found an identical discussion, but
the person said to check the properties of SQL Server Agent and then
look at Mail. Is there supposed to be a Mail tab or is it the "Mail Session"
box under the General tab (where I have the Mail Profile name).

Thanks for any help.

Ed

View Replies !   View Related
SQL - Email
Is there a way SQL server to email/send a query results to a user ?

Thanks in advance.

Ivan

View Replies !   View Related
Sql &#043; Email
Hello everyone

I am working on a little project for work which get the relvant data every 2mins and puts the data into a Data Grid in ASP.net 2.0

Now my SQL scipt runs every 2min and i need to know if at a certain time i could tell the SQL script to run the report and email me the results???

any help would be great

thanks alistair

View Replies !   View Related
DTS And Email
Hi all,

I am using SQL 2000 and i have DTS packages that are used to trasfer data from a datasource to SQL server. i want to set up the packges so i get an email for every step that failes, so i use on failure send email for each step. the question is what do i use to send the email, i know DTS has a MailTask but is this the only option and how is that different from XP_sendmail??

Appreciate any help
thanks

View Replies !   View Related
Email From Ftp
I have tried a few different stored procedures but i'm looking for away to send email from a server that does not have email enabled. (I want to pass in the smtp server and email and password.

Does anyone know of any script?

View Replies !   View Related
Email
EXEC master.dbo.xp_sendmail
@recipients=N'myemailaddress@somewhere.com',
@message=N'job failed.' ;

Do you know why the following error is produced?
xp_sendmail: Procedure expects parameter @user, which was not supplied.

Thanks

View Replies !   View Related
Email Not Being Sent...
I was attempting to send an e-mail from a package and it was not working correctly when I deployed to the remote DB server. I would simply get the "Failed sending mail" message.

I was setting the SmtpServer property of the connection through a configuration file to the remote SMTP server IP address (was 127.0.0.1 for development).

It turned out that the configuration setting was applied to the SMTP Connection Manager and the connection string would return the changed IP address BUT it was still connecting to the development 127.0.0.1 IP address. Once I changed the configuration property to be the connection string that was configured it functioned just fine.

I will be logging this as a bug with MS but thought people might want to know about this...

Fred

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved