Sql 2005 Trigger Insert And Dbmail

Apr 23, 2008

I have a stored proc that inserts records into a table, gets the identity, and uses that identity on a .net webpage. The page retrieves that identity key and processes some stuff on the page.

Everything was working fine until I tried to enable sql mail with triggers. I wanted to double-check some stuff, so I requested an sql email be sent whenever an insert occurs on the table above. Here's what I think is happening --- please correct and/or help me out with a work around?

I use the stored proc to insert a record.
I select the @@identity.
The trigger fires but uses select to retrieve the latest insert - thereby replacing the @@identity number returned to the page?

If this is true, could I do something like the following in the original stored proc? Is this a good idea or bad idea?

BEGIN
Declare @myID as int, @myBody1 as varChar(200)
Set @myID=0
INSERT INTO table (fields) VALUES (@PID, more stuff);
Set @myID = SELECT @@IDENTITY As [Identity];
If @myID<>0
Begin
Set @body1='<br />pid=' + more stuff.....
Exec msdb.dbo.sp_send_dbmail
@profile_name='profileName',
@recipients='email@email.com',
@subject='Temp History Insert',
@body=@body1,
@body_format= 'HTML' ;
End
END

View 3 Replies


ADVERTISEMENT

Trigger Insert And Dbmail

Apr 23, 2008

I have a stored proc that inserts records into a table, gets the identity, and uses that identity on a .net webpage. The page retrieves that identity key and processes some stuff on the page.

Everything was working fine until I tried to enable sql mail with triggers. I wanted to double-check some stuff, so I requested an sql email be sent whenever an insert occurs on the table above. Here's what I think is happening --- please correct and/or help me out with a work around?

I use the stored proc to insert a record.
I select the @@identity.
The trigger fires but uses select to retrieve the latest insert - thereby replacing the @@identity number returned to the page?

If this is true, could I do something like the following in the original stored proc? Is this a good idea or bad idea?

BEGIN
Declare @myID as int, @myBody1 as varChar(200)
Set @myID=0
INSERT INTO table (fields) VALUES (@PID, more stuff);
Set @myID = SELECT @@IDENTITY As [Identity];
If @myID<>0
Begin
Set @body1='<br />pid=' + more stuff.....
Exec msdb.dbo.sp_send_dbmail
@profile_name='profileName',
@recipients='email@email.com',
@subject='Temp History Insert',
@body=@body1,
@body_format= 'HTML' ;
End
END

View 7 Replies View Related

Need Help With Permissions Error On Dbmail Trigger

Sep 26, 2007

Good morning,

I'm stumped on this trigger error and I'm hoping someone can help. Here's the background:

BACKGROUND
Running SQLServer2005 on WinServer 2003 RC2. We use a SQL-based business application that has it's own alert system that uses database mail sucessfully, meaning the built-in emailing functions of the application work and can communicate with the database mail profile and send mail without error.

PROBLEM
I'm trying to write a trigger that will automatically send out an email alert after certain actions are performed in the business application. When I enable the trigger and it tries to run it fails with the following error:

SQLDBCode: 229Alerts error: SQLSTATE = 4200Microsoft OLE DB Provider for SQL ServerEXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'...

TROUBLESHOOTING
I've seen several articles about DatabaseMailUserRole permissions and have made sure that all users, admin and even guest (for testing) are members of this role. Other than that our setup is pretty vanilla so I'm not sure what else to do.

We only have one large dbase for the app and the fact that the internal emailing fuction works makes me think that the permissions for the msdb are already correct (although I could be wrong)

This problem is driving me crazy so I thank you in advance for any suggestions!

View 3 Replies View Related

SQL 2005 DBMail Security Question

Dec 7, 2007

Can a regular user configure DBMail on a SQL 2005 Server?

Thanks

View 1 Replies View Related

Sending SQL 2005 Dbmail When Using Application Role

Jan 16, 2007

I am attempting to use dbmail from an application that logs in to my database using an application role.  Since the application role does not exist outside the database, I created an spSendEmail in the database with "execute as login='mailagent'" in it:  I set the database to trustworthy, created a 'mailagent' account and added it to the msdb database with the databasemailuser role rights.

Email works just fine on the server when I use the execute as login='mailagent' to fire off the msdb..sp_send_dbmail.  But from the .NET application, I get the error: "Cannot execute as the server principal because the principal 'mailagent' does not exist, this type of principal cannot be impersonated, or you do not have permission."  When I run my spSendEmail stored procedure from the calling database, I get the same error.

View 1 Replies View Related

SQL 2005 : AFTER INSERT - Trigger

Jun 7, 2007

Is it possible to create a trigger in one database, that after aninsert, will update a database on a different server?If so, how would I do this?Thanks.Bill

View 1 Replies View Related

SQL Server 2005: TRIGGER AFTER INSERT

Mar 22, 2006

Hello,I am learning SQL Server 2005.I need to create a trigger which increments number of book'spublications:CREATE TRIGGER InsertPublicationON PublicationsAFTER INSERTASBEGINSET NOCOUNT ON;DECLARE @Num smallintSET @Num = SELECT NumPublications FROM Books WHERE ISBN IN(SELECT ISBN FROM inserted);UPDATE BooksSET NumPublications = @Num + 1WHERE ISBN IN(SELECT ISBN FROM inserted);ENDUnfortunately I receive a message:Incorrect syntax near the keyword 'SELECT'.Could you explain me please how to correct the code?I am new to SQL Server.Thank you very much./RAM/

View 2 Replies View Related

AFTER INSERT Trigger Not Firing In SQL 2005

Jul 28, 2006

Nothing fancy; just a trigger on a sharepoint table that supposed towrite a record to another SQL table.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER TRIGGER [TV_UpdateFileSyncProgress]ON [dbo].[Docs]AFTER INSERTASBEGINSET NOCOUNT ON;BEGINIF EXISTS (SELECT null FROM inserted WHERE DirName like'csm/%/Shared Documents')BEGINIF NOT EXISTS (SELECT null FROM inserted INNER JOINTV_FileSyncProgress fp ON LOWER(RTRIM(fp.LeafName)) =LOWER(RTRIM(Replace(Replace(inserted.DirName,'csm/',''),'/SharedDocuments','') + '' + inserted.LeafName)))BEGININSERT INTO TV_FileSyncProgress (InternalOrigin, ExternalOrigin,ChangeType, SiteId, DirName, LeafName, FlagForDelete)SELECT0,1,1,SiteId,'F:commonExtranet',Replace(Replace (DirName,'csm/',''),'/SharedDocuments','') + '' + LeafName,0 FROM insertedENDENDENDEND

View 1 Replies View Related

Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table

Oct 30, 2007

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 17 Replies View Related

Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table

Feb 5, 2008

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 9 Replies View Related

Interaction Between Instead Of Insert Trigger And Output Clause Of Insert Statement

Jan 14, 2008


This problem is being seen on SQL 2005 SP2 + cumulative update 4

I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable

I now need to add an "instead of insert" trigger to the table that is the subject of the insert.

As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows

Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table

Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.

To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row

I need the behaviour to be correct when the trigger is present

Any thoughts would be much appreciated

aero1


/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)

GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN

INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted

END

/************************************************
3) - Do the insert
************************************************/

DECLARE @my_insert TABLE( my_table_id bigint )

declare @forename VARCHAR(100)
declare @surname VARCHAR(50)

set @forename = N'john'
set @surname = N'smith'

INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)

select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger

/************************************************
4) - Drop the trigger
************************************************/

drop trigger [dbo].[trig_my_table__instead_insert]
go

/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK

View 5 Replies View Related

Multiple Insert Call For A Table Having Insert Trigger

Mar 1, 2004

Hi

I am trying to use multiple insert for a table T1 to add multiple rows.

Ti has trigger for insert to add or update multiple rows in Table T2.

When I provide multiple insert SQL then only first insert works while rest insert statements does not work

Anybody have any idea about why only one insert works for T1

Thanks

View 10 Replies View Related

DBMail

May 16, 2006

Is it possible to embed a image datatype into a EMail message using sp_Send_DBMail?

For example, my query would select a saved print screen image held in a SQL table as datatype image. I would prefer not to attach this image but rather have it print in the message section.

Thanks in advance.

View 2 Replies View Related

DBMail

Jan 30, 2008

Hi,
I am using SQL Server 2005 and need to mail. I used sp_send_dbmail for mailing but I am able send to one @recipeint and one @copy_recipient.
How can I mail to many email addresses and/or group?

Any help is appreciated.
Thank you,

View 6 Replies View Related

Insert Trigger For Bulk Insert

Nov 25, 2006

In case of a bulk insert, the “FOR INSERT� trigger fires for each recod or only once?
Thanks,

View 1 Replies View Related

Get DBMail Output?

May 30, 2013

With Query Anylyser, I am sending mails with DBMail successfully. Each time mail is sent, bottom half window messages tab shows

"Mail (Id: 30242) queued."

Is there any way to capture Mail (Id: 30242) and update the same in a table?

View 1 Replies View Related

HTML And DBmail

Nov 29, 2007

Is anyone using a SP or Trigger to Create a HTML formated e-mail and send it useing DBmail?

If so could I see a sample of the code?

Jim
Users <> Logic

View 4 Replies View Related

SQL DBMAIL Performance Issues

May 26, 2007

Hi,

I'd like to have some feedback from the experts on SQL 2005.

The situations is the following.

I have an application .net/SQL server 2000 that send notification mails using the SQL mail feature. It works fine for years.

Sooner or later SQL 2000 will be out of service, so I decide to move my database to SQL 2005.

When I'm trying to put in production with my web hosting provider www.webhost4life.com, they said that are not supporting SQL DBMAIL on any SQL 2005 32/64 bits because the extra workload will slow down SQL 2005 performance.

My question is if somebody has experience of this situation in other installations or maybe is that provider who has some problems tuning the SQL servers.

View 1 Replies View Related

Config DBmail To Use MX Records?

Sep 11, 2007

We are having trouble configuring SQL Server 2005 to use MX records.
Basically, we want to use DBmail to utilize multiple mail relays.
The three mail relays are contained within internal DNS MX records.

smtphost.xxx.com. IN MX 10 mailhost1.xxx.com.
smtphost.xxx.com. IN MX 10 mailhost2.xxx.com.
smtphost.xxx.com. IN MX 10 mailhost3.xxx.com.

It doesn't seem to like using MX records? Any ideas?
Thanks KraftR
Tim.Kraft@Dowjones.com

View 1 Replies View Related

DBMail Permission Error

Feb 18, 2006

I am using DBMail to send an email notification at the completion of service broker process and have been getting the following security error:


Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1

EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

I have given the userID used to run the Service Broker, databasemailuserrole in msdb as per instruction in the msdb. I have also given it execute permission on the sp_send_dbmail but keep getting the error. The service broker is run using a sql login and the dbmail profile is set as public profile. Any suggestions would be much appreciated.

View 6 Replies View Related

Problem With Service Broker And DBMail

Sep 20, 2006

Hi!
I try to use Service Broker and DBMail together, but have some trouble with that.
I need to create the queue with activation.
And the stored procedure activated on this queue must send e-mail using DBmail.
It's looks simple, but it doesn't work.
There is my script to create objects, but don't forget create dbmail profile before use it.
PS And replace my email by yours

View 1 Replies View Related

SQL 2012 :: Any Way To Use People Distribution List With DBMail?

Mar 13, 2015

I would like to replace mymail@mail.com by mypdl@mail.com , where mypdl is basically a people distribution list with 2 emails: my own email and my coworker and fellow DBA.

IF EXISTS
(
SELECT TOP 1 [PercentUsed]
FROM [BackupSize]
WHERE PercentUsed>=70 AND [drivename]='D:' -- D is the internal drive
ORDER BY [MetricDate] DESC

[Code] ...

But I think this won't work by design. Maybe I need to create an operator?

View 4 Replies View Related

Insert Trigger - How To

Nov 20, 2006

 I would like to have the value of a field to be set the return value of
System.Web.Security.Membership.GeneratePassword(12,4)
every time a a row is inserted.
Can you guide with this?
Do you have  some similar sample code?
Thank you very much

View 1 Replies View Related

Insert Trigger

Dec 3, 2006

I would like to have the value of a field to be set the return value of
System.Web.Security.Membership.GeneratePassword(12,4)
every time a a row is inserted.
Can you guide with this?
Do you have  some similar sample code?
Thank you very much

View 1 Replies View Related

Insert Trigger

Nov 12, 2001

Should a insert trigger fire my update trigger as well? it fires it automatically after the insert? Is this a bug?

View 1 Replies View Related

Insert Trigger (Help)

Aug 31, 2000

I have to create a insert trigger that calls a stored procedure usp_a
I need to pass two parameters to this stored procedure, value coming from the row inserted...
What is the syntax? Any help will be appreciated?

View 1 Replies View Related

Insert Trigger Help

Jan 14, 2005

Hi.

Replication is not an option for me so I am trying to creat a trigger that will basically copy any new record to another database. I am using an on insert trigger and I get all records from the table inserted to the new db not just the new record.

Any ideas?

Thanks.

Bill

View 4 Replies View Related

Pre-insert Trigger

Apr 26, 1999

I am trying to write a pre-insert trigger. I want a row to be deleted first and then have the new row inserted. The end result is an insert/update statement. If anyone knows how to do this or has a better way, let me know, Please.

View 3 Replies View Related

Insert Trigger

Oct 13, 1998

I am trying to write an insert trigger for the following problem. I have a table that contains a field (amount) that contains currency amount. Each row could be a record from a transaction in a different country. I would like each record to be converted into U.S. currency as it is inserted. So I created an other table that contains that exchange rate. How can I create a trigger that does this?

Tax Detail Table Exchange Table

Account# int Country char
Description char ExchangeRate Money
Amount money
Country char

I am working with MSS 6.5. Any and all help will be greatly appreciated.

Fidencio Peña

View 3 Replies View Related

ON INSERT TRIGGER

Dec 8, 1998

MSSQL 6.5

Is there any way to change some columns on inserted lines in ON INSERT trigger?

My notice: I try to find something about it in SQLBOOKS, but I found nothing.
It's possibly not TRUEEE!!!!! It wants me to grumble.


Thank for your answer.

View 1 Replies View Related

Trigger INSERT Q

Nov 28, 2005

Really simple question - I have the following trigger for the INSERT event for Tbl_B:

CREATE TRIGGER calc_total ON [dbo].[Tbl_B]
FOR INSERT
AS

DECLARE @VATRate AS decimal(19,2)
SET @VATRate = (
SELECT Val
FROM Tbl_Numeric_Refs
WHERE Ref = 'VATRate'
)

UPDATE Tbl_B
SET [Total] = ((@VATRate / 100) * [PriceA]) + [PriceA],
[VATRate] = @VATRateHow can I restrict the UPDATE within the trigger to the record(s) being INSERTed? Or am I over-complicating things, and should use a simple UDF for this instead?

View 2 Replies View Related

Trigger On Insert

Oct 22, 2007

Is it possible to pick up the value that is being inserted within the scope of a trigger? For example, if I were to run the following

INSERT INTO people (firstname, lastname)
VALUES ('George', 'V')

Would it be possible to access these values in a trigger before they are inserted?

CREATE TRIGGER trigger1
ON people
FOR INSERT
AS
IF EXISTS (SELECT 1 FROM table1 WHERE firstname = <the value being inserted>) BEGIN
Print '1'
END
ELSE BEGIN
Print '2'
END
GO

View 4 Replies View Related

SQL Trigger To Run On Insert Only

Nov 15, 2007

Rightio - this is probably a simple question for you SQL afficionados.I have written a trigger to update a master table with a CreateDate field. Simple enough I thought but it updates this field when the existing record is edited as well - not so good.This is how it looks like:CREATE TRIGGER CreateDateON MasterFOR UPDATE ASDECLARE@idMaster intSELECT @idMaster = idMaster from Insertedupdate Masterset CreatedDate = getdate()where idMaster = @idMasterGOWell I know I can write an IF statement that will basically say if the field is not null then only update - fair enough - but is there a simpler way to do this? Is there a way I need to write my CREATE TRIGGER statement that ONLY makes it run when it is a NEW INSERT ONLY?THANKS!

View 8 Replies View Related







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