Sp_send_dbmail Trigger On Multiple Row Insert

Feb 15, 2008

Hi,


I have a SQL Agent Job that selects records that are of a particular age (from Table1) and inserts them into another table (Table 2) - multiple records get inserted as a single INSERT step. On Table 2 I then have a trigger set FOR INSERT which I was hoping would send an email using the fields copied from the batch job, one of which being an email address.


The trigger is: -




Code Snippet
CREATE TRIGGER trgSendMail ON Table2 FOR INSERT AS
DECLARE @myEmail VarChar(50)
SELECT @myEmail = strEmail FROM Inserted
EXEC sp_send_dbmail
@Profile_Name = '{MailProfile}',
@Recipients = @myEmail,
@Subject = '{MailTitle}',
@Body = '{MailBody}'






Thing is, the agent works fine but the trigger only sends an email to the first row inserted.
Any ideas on how to get around this so that if the batch job inserts 10 rows into Table2 then 10 emails are sent out?

Shaun.

View 6 Replies


ADVERTISEMENT

Trigger Problem With Sp_send_dbmail

May 15, 2008

Hallo

I want to write a trigger that sends me an email, when new records are added into a table.

For testing I have created one table called location1, which has simple data from AdventureWorks.Production.Location. And another table is called new_location, which stores the newly added records.

Here is the trigger:********************************
use [AdventureWorks]
if object_id ('location1') is not null
drop table location1
go
create table location1(LocationID int, LName varchar(50))

if object_id ('new_location') is not null
drop table new_location
go
create table new_location
(LocationID int, LName varchar(50))

if object_id ('change_ID', 'TR') is not null
drop trigger change_ID
go
create trigger change_ID on location1
for insert
as
delete new_location
insert into new_location
select * from inserted

exec sp_send_dbmail
@profile_name = 'Profile',
@recipients = 'email',
@subject = 'New Record(s) added.',
@query = 'select * from new_location order by LocationID';
go
********************************

After I insert statement, Management Studio runs the executing throughout.
insert into location1
select LocationID, Name
from Production.Location
order by LocationID

Can somebody help?









View 2 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

Insert Multiple Rows With A Trigger That Invoke A Function

Jan 17, 2012

Multiple rows to insert:
---------------------
insert into Customer(CustomerId,Name,Value)
select CustomerId,Name,Value
from CustomerTemp

Trigger in Customer table that invoke a function:
alter TRIGGER [dbo].[Calculation] ON [dbo].[Customer]
AFTER INSERT
AS

update Customer
set Percentage = dbo.GetPercentage((select Value from inserted))
where CustomerId = (select CustomerId from inserted)

I'm getting the error for the multiple row.Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Is there a way to let me insert multiple rows, using the trigger that invoke a function ?

View 1 Replies View Related

SQL Server 2012 :: Trigger Inserted Multiple Rows After Insert

Aug 6, 2014

I create a Trigger that allows to create news row on other table.

ALTER TRIGGER [dbo].[TI_Creation_Contact_dansSLX]
ON [dbo].[_IMPORT_FILES_CONTACTS]
AFTER INSERT
AS

[code]...

But if I create an INSERT with 50 rows.. My table CONTACT and ADDRESS possess just one line.I try to create a Cursor.. but I had 50 lines with an AdressID and a ContactID differently, but an Account and an AccountId egual on my CONTACT table :

C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC001 - ACCOUNT 001
C003 - AD003 - AC001 - ACCOUNT 001
C004 - AD004 - AC001 - ACCOUNT 001
C005 - AD005 - AC001 - ACCOUNT 001

I search a means to have 50 lines differently on my CONTACT table.

C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC002 - ACCOUNT 002
C003 - AD003 - AC003 - ACCOUNT 003
C004 - AD004 - AC004 - ACCOUNT 004
C005 - AD005 - AC005 - ACCOUNT 005

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

Integration Services :: Insert Multiple Columns As Multiple Records In Table Using SSIS?

Aug 10, 2015

Here is my requirement, How to handle using SSIS.

My flatfile will have multiple columns like :

ID  key1  key2  key3  key 4

I have SP which accept 3 parameters ID, Key, Date

NOTE: Key is the coulm name from the Excel. So my sp call look like

sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date

View 7 Replies View Related

Multiple Insert Into Multiple Tables With A Stored Procedure

Mar 1, 2007

Hello
I am building a survey application.
 I have 8 questions. 
 Textbox -  Call reference
 Dropdownmenu  - choose Support method
 Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
 

View 9 Replies View Related

Insert Single Row / Multiple Rows Into Multiple Tables

Sep 3, 2014

How to insert single row/multiple rows into multiple tables by using single insert statement.

View 1 Replies View Related

Bulk Insert Multiple Files To Multiple Tables - How?

Feb 15, 2008

I need to be able to bulk insert a bunch of tables from their corresponding flat file. I have created an XML file (see below) which has the file name/table name pair at each node. I then created a ForEachLoop task and used the Node enumeration type and the following OuterXpathString: ReferenceFiles/File. At this point I get lost. How do I pass the 2 inside node values (file name and table name) to variables which I can then use as expressions for the bulk insert task inside the Foreach?

Here is XML file:




Code Snippet
<ReferenceFiles>

<File>


<FileName>Ref_Categories.txt</FileName>
<TableName>Ref_Categories</TableName>
</File>
<File>

<FileName>Ref_Configs.txt</FileName>
<TableName>Ref_Configs</TableName>
</File>
</ReferenceFiles>






Thanks.

View 1 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

Wants To Insert Into Multiple Table By A Single Insert Query

Apr 11, 2008

Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks

View 3 Replies View Related

Is Multiple Action Trigger Possible

Apr 8, 2007

Hello
Is it possible to create a trigger with multiple actions?
I would like to create a trigger with INSERT, DELETE, UPDATE funtions but I have not been above to find a clear syntax example
Below I have created a statement of my trigger.  Could somebody please confirm if the syntac in this multiple action trigger is ok.multple actions on a single trigger
Is this syntax correct?
CREATE TRIGGER [dbo].[trig_AddDomCatA] ON DomainNames For INSERT, DELETE, UPDATE
AS
INSERT INTO Domain_CatA (DomainName)SELECT DomainName FROM INSERTED
ASDELETE FROM Domain_CatA (DomainName)SELECT DomainName FROM DELETED
ASUPDATE INTO Domain_CatA (DomainName)SELECT DomainName FROM UPDATED
 Thanks
Lynn
 
 
 

View 7 Replies View Related

Trigger For Multiple Db Update

Oct 30, 2003

I have a messy design that I can't change but need to keep in synch. I have a master table where a person's info is entered into. Upon that entry I have to take that info (name, bdate,ssn,location) and populate it to 4 separate tables in 4 separate databases, all of which have their own id field which has to be incremented (no none of them are identity columns). At the end of the process I have to update the master record with the id fields of all the other four tables. I was going to do this with an insert trigger. As far as protecting the id's across all four databases, I was going to use a begin trans and get the next id field for all four databases and then apply the logic to add the records. Does this seem a safe approach, or am I missing something? Do I need to do a begin tran for each database separately?
THanks

View 1 Replies View Related

One Trigger Vs Multiple Triggers ?

Aug 17, 2007



I have a question regarding Triggers.

Lets say I want to create a trigger for Insert, Update and Delete action. For each action I do different things.

Now I can create a single trigger for Insert, delete and update, and using counts for inserted and deleted table, take the action appropriately.

Or I can create three different triggers each for Insrt, update and delete.

Which option is better for performance?

Thanks



View 2 Replies View Related

Trigger With Multiple Rows

Jun 1, 2006

Hi i've searched a lot with no results, im trying to do a trigger, this is the scenario

Got 3 tables, Product, Lines, Sublines

If Update Multiple sublines it should be updated to the products table, but i cant because the inserted table has multiple rows, the only way to do this is using cursors???

(Note, i cant do this using cascade because there are others, if i include this get a circular problem so i have to solve this other way)

View 1 Replies View Related

Multiple Connections In A CLR Trigger

May 23, 2007

Hello

Is there nay way that it is possible to connect to another database within a trigger€¦

My scenario is that an update in a table in database X triggers several updates in a table in Database Y. The databases are on the same SQL Server

Thanks for any ideas, solutions or hints you can give me..!!!

View 5 Replies View Related

Sp_send_dbmail

May 9, 2006

When using "sp_send_dbmail", messages are queued through Service Broker. Other than the system views, "dbo.sysmail_...", in MSDB, is there another way to know if an email was sent successfully?

Also, in 2000 you did not need to supply the whole email address in order for xp_sendmail to work. For example, I could use my name, "RGioia" as the recipient and it would send email to rgioia@<my current domain>. Does anyone know of a work-around for this in 2005 or do you just have to ensure that full email addresses are used?

Thanks in advance!!

View 1 Replies View Related

SP_SEND_DBMAIL

Aug 8, 2007

I wrote a trigger as follows:

CREATE TRIGGER TR_ABC
ON TABLE_A
AFTER INSERT
AS
DECLARE @E_MAIL VARCHAR(255),
@MESSAGE VARCHAR(255)
SET @MESSAGE = 'A new call request has been logged'

SELECT @E_MAIL = E_MAIL
FROM TABLE_A AS A
LEFT OUTER JOIN TABLE_B AS B
ON B.SYSADMIN = A.SYSADMIN
WHERE SYS_ADMIN = 1

EXEC MSDB.DBO.SP_SEND_DBMAIL
@profile_name = 'SCINFO',
@recipients = @E_MAIL,
@body = @MESSAGE,
@subject = 'SCI Service Request';


The problem is everytime I insert data into the table I get an error stating that-:
'At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients".
Mail queued.'

Please help.

View 1 Replies View Related

Need Help W/ Sp_send_dbmail !

May 1, 2007

SQL Server 2005Help. I have a problem:On a 2000 box, I am calling the sp_send_dbmail stored procedure on a2005 box (they are linked servers).This is how I'm calling the proc (from the 2000 box), which usessp_send_dbmail to send the mail.EXEC My2005Server.DatabaseName.dbo.SendNotification@recipients = 'me@yahoo.com',@subject = 'SENDING @TOTAL,@body = @text1 + @text2 + @text3 + @text4 + @text5@text1 - @text5 are varchar(8000).As you can see I'm trying to take advantage of the varchar(max) that@body allows.However, I get this error:Server: Msg 170, Level 15, State 1, Line 22Line 22: Incorrect syntax near '+'.I need to be able to concatenate these somehow... as I cannot put theminto one large variable from the 2000 server side.Can you see the problem here?Help.Thanks

View 1 Replies View Related

Sp_send_dbmail

Oct 31, 2007

Hello all,

I'm new to this forum and have the same question on SQLTeam.com, where I'm a frequent forum user.

I'm trying to send the results of a table to a group of people by email, see code used below:

exec msdb.dbo.sp_send_dbmail @profile_name = 'DT', @recipients = 'isantos@foo.net;', @subject = 'QA Results', @body = 'Some Text', @query = 'select * from [db].[dbo].[table]', @execute_query_database = 'db', @exclude_query_output = 1, @append_query_error = 1;


Error message I get by email:

Msg 15404, Level 16, State 19, Server TAKKARA, Line 1 Could not obtain information about Windows NT group/user 'WECLICKisantos', error code 0x5.

If I remove the @query, @exclude_query_output and @append_query_error options, I can send the email without a problem, just whenever I try to add a query to it that it gives me this error message.

I'm a sysadmin, I can run that query, I have access to the DatabaseMailUserRole under msdb and the profile that I'm using is public... I'm kind of stuck, let me know if you can help.

View 3 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







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