|
Home > Archive > MS SQL Server > November 2006 > Triggers
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Ben Watts 2006-11-08, 7:12 pm |
| I have an update triiger on one of my tables and whenever that table is
updated it sends an email. I didnt create this trigger, now my question is
this. It was sending an email to an employee that no longer works here.
Where can I find out where this trigger is set to send to a specific email
address. I see nothing in the actual trigger that specifies and email
address. Please help
| |
| sqldba 2006-11-08, 7:12 pm |
| Can you post the DDL in the trigger please.
Charles Deaton
www.sqlsig.org
Ben Watts wrote:
> I have an update triiger on one of my tables and whenever that table is
> updated it sends an email. I didnt create this trigger, now my question is
> this. It was sending an email to an employee that no longer works here.
> Where can I find out where this trigger is set to send to a specific email
> address. I see nothing in the actual trigger that specifies and email
> address. Please help
| |
| sqldba 2006-11-08, 7:12 pm |
| Can you post the logic in the trigger please.
Charles Deaton
www.sqlsig.org
Ben Watts wrote:
> I have an update triiger on one of my tables and whenever that table is
> updated it sends an email. I didnt create this trigger, now my question is
> this. It was sending an email to an employee that no longer works here.
> Where can I find out where this trigger is set to send to a specific email
> address. I see nothing in the actual trigger that specifies and email
> address. Please help
| |
| Arnie Rowland 2006-11-08, 7:12 pm |
| Is it possible that there is a SQL Agent Alert instead of the Trigger that
is sending the email?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Ben Watts" <ben. watts@aaronnickellho
mes.com> wrote in message
news:uArQum1AHHA.996@TK2MSFTNGP02.phx.gbl...
>I have an update triiger on one of my tables and whenever that table is
>updated it sends an email. I didnt create this trigger, now my question is
>this. It was sending an email to an employee that no longer works here.
>Where can I find out where this trigger is set to send to a specific email
>address. I see nothing in the actual trigger that specifies and email
>address. Please help
>
>
| |
| Ben Watts 2006-11-08, 7:12 pm |
| set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].& #91;EmailOnUpdateSTA
TUS_AND_DATES]
on [dbo].& #91;STATUS_AND_DATES
]
FOR Update
AS
DECLARE @newvalue as varchar(8000)
,@oldvalue as varchar(8000)
,@sendBody as varchar(8000)
Select @newvalue = 'New Value: ' +(SELECT ISNULL(& #91;WWPJobNumber],''
)
+'|'+ISNULL([Job_Status],'')
+'|'+ISNULL(CAST(& #91;Const_Start_Date
] as varchar(20)),'')
+'|'+ISNULL(CAST(& #91;Const_Finish_Dat
e] as varchar(20)),'')
+'|'+ISNULL(CAST(& #91;Final_Inspect_Da
te] as varchar(20)),'')
+'|'+ISNULL(CAST([CO_Received] as varchar(20)),'')
+'|'+ISNULL(CAST(& #91;Warranty_Start_D
ate] as varchar(20)),'')
+'|'+ISNULL(CAST(& #91;Warranty_Expire_
Date] as varchar(20)),'')
+'|'+ISNULL(CAST([Listing_Date] as varchar(20)),'')
+'|'+ISNULL(CAST(& #91;Received_Contrac
t_Date] as varchar(20)),'')
+'|'+ISNULL(CAST(& #91;Signed_Contract_
Date] as varchar(20)),'')
+'|'+ISNULL(CAST([Closing_Date] as varchar(20)),'')
+'|'+ISNULL(& #91;Early_Occupancy]
,'')
+'|'+ISNULL([Paint_Kit],'')
+'|'+ISNULL(CAST([Keys_Received] as varchar(20)),'')
+'|'+ISNULL(CAST(& #91;Garage_Remote_Re
ceived] as varchar(20)),'')
+'|'+ISNULL(CAST([Model_Home] as varchar(20)),'')
+'|'+ISNULL(CAST([Demastered] as varchar(20)),'') as Insertedstuff
FROM Inserted);
Select @oldValue = 'Old Value: '+(SELECT ISNULL(& #91;WWPJobNumber],''
)
+'|'+ISNULL([Job_Status],'')
+'|'+ISNULL(CAST(& #91;Const_Start_Date
] as varchar(20)),'')
+'|'+ISNULL(CAST(& #91;Const_Finish_Dat
e] as varchar(20)),'')
+'|'+ISNULL(CAST(& #91;Final_Inspect_Da
te] as varchar(20)),'')
+'|'+ISNULL(CAST([CO_Received] as varchar(20)),'')
+'|'+ISNULL(CAST(& #91;Warranty_Start_D
ate] as varchar(20)),'')
+'|'+ISNULL(CAST(& #91;Warranty_Expire_
Date] as varchar(20)),'')
+'|'+ISNULL(CAST([Listing_Date] as varchar(20)),'')
+'|'+ISNULL(CAST(& #91;Received_Contrac
t_Date] as varchar(20)),'')
+'|'+ISNULL(CAST(& #91;Signed_Contract_
Date] as varchar(20)),'')
+'|'+ISNULL(CAST([Closing_Date] as varchar(20)),'')
+'|'+ISNULL(& #91;Early_Occupancy]
,'')
+'|'+ISNULL([Paint_Kit],'')
+'|'+ISNULL(CAST([Keys_Received] as varchar(20)),'')
+'|'+ISNULL(CAST(& #91;Garage_Remote_Re
ceived] as varchar(20)),'')
+'|'+ISNULL(CAST([Model_Home] as varchar(20)),'')
+'|'+ISNULL(CAST([Demastered] as varchar(20)),'') as Deletedstuff
FROM Deleted);
Set @sendbody =
@oldValue+char(13)+c
har(10)+char(13)+cha
r(10)+@newValue+char
(13)+char(10)+User_N
ame();
EXECUTE pr_SendTriggerMail @sendbody, 'STATUS_AND_DATES Items Updated';
"Ben Watts" <ben. watts@aaronnickellho
mes.com> wrote in message
news:uArQum1AHHA.996@TK2MSFTNGP02.phx.gbl...
>I have an update triiger on one of my tables and whenever that table is
>updated it sends an email. I didnt create this trigger, now my question is
>this. It was sending an email to an employee that no longer works here.
>Where can I find out where this trigger is set to send to a specific email
>address. I see nothing in the actual trigger that specifies and email
>address. Please help
>
>
| |
| Ben Watts 2006-11-08, 7:12 pm |
| How do I check for the alert
"Arnie Rowland" <arnie@1568.com> wrote in message
news:eXoZ0w1AHHA.4348@TK2MSFTNGP04.phx.gbl...
> Is it possible that there is a SQL Agent Alert instead of the Trigger that
> is sending the email?
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
>
> "Ben Watts" <ben. watts@aaronnickellho
mes.com> wrote in message
> news:uArQum1AHHA.996@TK2MSFTNGP02.phx.gbl...
>
>
| |
| sqldba 2006-11-08, 7:12 pm |
| The trigger is calling what looks like a stored procedure
EXECUTE pr_SendTriggerMail @sendbody, 'STATUS_AND_DATES Items Updated';
This is where the email is coming from.
Ben Watts wrote:[color=darkred
]
> How do I check for the alert
> "Arnie Rowland" <arnie@1568.com> wrote in message
> news:eXoZ0w1AHHA.4348@TK2MSFTNGP04.phx.gbl...
| |
| Ben Watts 2006-11-08, 7:12 pm |
| where do i check that out at? Just learning all of this
"sqldba" <sqldba@comcast.net> wrote in message
news:1163008161.158559.127840@f16g2000cwb.googlegroups.com...
> The trigger is calling what looks like a stored procedure
> EXECUTE pr_SendTriggerMail @sendbody, 'STATUS_AND_DATES Items Updated';
>
> This is where the email is coming from.
>
> Ben Watts wrote:
>
| |
| sqldba 2006-11-08, 7:12 pm |
| >From with in the database where you found the trigger click on Stored
Procedures and look for pr_SendTriggerMail. Double click it and you
should the old email address some place.
Ben Watts wrote:[color=darkred
]
> where do i check that out at? Just learning all of this
> "sqldba" <sqldba@comcast.net> wrote in message
> news:1163008161.158559.127840@f16g2000cwb.googlegroups.com...
| |
| Ben Watts 2006-11-08, 7:12 pm |
| Still cant find anything about soted procedures. Give it to me in lamans
terms. Sorry.
"sqldba" <sqldba@comcast.net> wrote in message
news:1163009032.078778.213640@h48g2000cwc.googlegroups.com...
> Procedures and look for pr_SendTriggerMail. Double click it and you
> should the old email address some place.
>
> Ben Watts wrote:
>
| |
| Charles Deaton 2006-11-08, 7:12 pm |
| Are you using Microsoft SQL Server Enterprise Manager? If so open up
the tree of servers in it then open the databases folder. Open the
database where you found the trigger and look for a folder named Stored
Procedures. In this folder you should be the procedure mentioned below.
Charles Deaton
www.SQLSIG.org
Ben Watts wrote:[color=darkred
]
> Still cant find anything about soted procedures. Give it to me in lamans
> terms. Sorry.
> "sqldba" <sqldba@comcast.net> wrote in message
> news:1163009032.078778.213640@h48g2000cwc.googlegroups.com...
| |
| Sue Hoegemeier 2006-11-09, 12:12 am |
| Look like you have a stored procedure that the trigger
executes - you'll need to check pr_SendTriggerMail
-Sue
On Wed, 8 Nov 2006 11:44:37 -0600, "Ben Watts"
<ben. watts@aaronnickellho
mes.com> wrote:
>set ANSI_NULLS ON
>
>set QUOTED_IDENTIFIER ON
>
>GO
>
>ALTER Trigger [dbo].& #91;EmailOnUpdateSTA
TUS_AND_DATES]
>
>on [dbo].& #91;STATUS_AND_DATES
]
>
>FOR Update
>
>AS
>
>DECLARE @newvalue as varchar(8000)
>
>,@oldvalue as varchar(8000)
>
>,@sendBody as varchar(8000)
>
>Select @newvalue = 'New Value: ' +(SELECT ISNULL(& #91;WWPJobNumber],''
)
>
>+'|'+ISNULL([Job_Status],'')
>
>+'|'+ISNULL(CAST(& #91;Const_Start_Date
] as varchar(20)),'')
>
>+'|'+ISNULL(CAST(& #91;Const_Finish_Dat
e] as varchar(20)),'')
>
>+'|'+ISNULL(CAST(& #91;Final_Inspect_Da
te] as varchar(20)),'')
>
>+'|'+ISNULL(CAST([CO_Received] as varchar(20)),'')
>
>+'|'+ISNULL(CAST(& #91;Warranty_Start_D
ate] as varchar(20)),'')
>
>+'|'+ISNULL(CAST(& #91;Warranty_Expire_
Date] as varchar(20)),'')
>
>+'|'+ISNULL(CAST([Listing_Date] as varchar(20)),'')
>
>+'|'+ISNULL(CAST(& #91;Received_Contrac
t_Date] as varchar(20)),'')
>
>+'|'+ISNULL(CAST(& #91;Signed_Contract_
Date] as varchar(20)),'')
>
>+'|'+ISNULL(CAST([Closing_Date] as varchar(20)),'')
>
>+'|'+ISNULL(& #91;Early_Occupancy]
,'')
>
>+'|'+ISNULL([Paint_Kit],'')
>
>+'|'+ISNULL(CAST([Keys_Received] as varchar(20)),'')
>
>+'|'+ISNULL(CAST(& #91;Garage_Remote_Re
ceived] as varchar(20)),'')
>
>+'|'+ISNULL(CAST([Model_Home] as varchar(20)),'')
>
>+'|'+ISNULL(CAST([Demastered] as varchar(20)),'') as Insertedstuff
>
>FROM Inserted);
>
>Select @oldValue = 'Old Value: '+(SELECT ISNULL(& #91;WWPJobNumber],''
)
>
>+'|'+ISNULL([Job_Status],'')
>
>+'|'+ISNULL(CAST(& #91;Const_Start_Date
] as varchar(20)),'')
>
>+'|'+ISNULL(CAST(& #91;Const_Finish_Dat
e] as varchar(20)),'')
>
>+'|'+ISNULL(CAST(& #91;Final_Inspect_Da
te] as varchar(20)),'')
>
>+'|'+ISNULL(CAST([CO_Received] as varchar(20)),'')
>
>+'|'+ISNULL(CAST(& #91;Warranty_Start_D
ate] as varchar(20)),'')
>
>+'|'+ISNULL(CAST(& #91;Warranty_Expire_
Date] as varchar(20)),'')
>
>+'|'+ISNULL(CAST([Listing_Date] as varchar(20)),'')
>
>+'|'+ISNULL(CAST(& #91;Received_Contrac
t_Date] as varchar(20)),'')
>
>+'|'+ISNULL(CAST(& #91;Signed_Contract_
Date] as varchar(20)),'')
>
>+'|'+ISNULL(CAST([Closing_Date] as varchar(20)),'')
>
>+'|'+ISNULL(& #91;Early_Occupancy]
,'')
>
>+'|'+ISNULL([Paint_Kit],'')
>
>+'|'+ISNULL(CAST([Keys_Received] as varchar(20)),'')
>
>+'|'+ISNULL(CAST(& #91;Garage_Remote_Re
ceived] as varchar(20)),'')
>
>+'|'+ISNULL(CAST([Model_Home] as varchar(20)),'')
>
>+'|'+ISNULL(CAST([Demastered] as varchar(20)),'') as Deletedstuff
>
>FROM Deleted);
>
>Set @sendbody =
> @oldValue+char(13)+c
har(10)+char(13)+cha
r(10)+@newValue+char
(13)+char(10)+User_N
ame();
>
>EXECUTE pr_SendTriggerMail @sendbody, 'STATUS_AND_DATES Items Updated';
>
>"Ben Watts" <ben. watts@aaronnickellho
mes.com> wrote in message
>news:uArQum1AHHA.996@TK2MSFTNGP02.phx.gbl...
>
|
|
|
|
|