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??



ADVERTISEMENT

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







Copyrights 2005-15 www.BigResource.com, All rights reserved