insert trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

insert trigger

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-09-15 : 11:53:26

hi

i have a table called customer having the following fields

customerid ,customername,products,amountpaid.

i want to create an insert trigger that sends out an email
whenever a new customer entry is made in the table with body of the mail containing the customer name products and amount paid.

how can it be done...thanks in advance...

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-09-15 : 18:28:02

hi everyone

iam having a peculiar problem .

i have two tables auditlog and tempauidtlog.

i have created a trigger on auditlog which inserts only those fields which i need into tempauditlog whenever a new entry is made in auditlog.
then i have a trigger on tempauidtlog which fires to sends a email whenever a new entry is logged into this tempauidtlog.

my trigger on auditlog is working and inserting info to tempauidtlog whereas the trigger on tempaudtlog is not firing at that time.

but when i manually insert a new record in tempauditlog,then the trigger is firing sending an email. I just can't figure what the problem is..Is it due to casacding of triggers..

thanks in advance
here is the code:



alter trigger alert2
on auditlog
after insert 
as
insert into tempauditlog
select customername,customerid,customer_products
from inserted
--




create TRIGGER [dbo].[alert]
ON [dbo].tempauditlog
after INSERT
AS
  declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
	@FROM			= N'MyEmail@MyDomain.com',
	@FROM_NAME		= N'Joe Mailman',
	@TO			    = 'akpaga@hotmail.com',
    @replyto        = N'Reply to Joe the Mailman',
	@CC			    = N'',
	@BCC			= N'',
	@priority		= N'NORMAL',
	@subject		= N'Hello SQL Server SMTP Mail',
	@message		= N'Goodbye MAPI and Outlook',
	@messagefile	= N'',
	@type			= N'text/plain',
	@attachment		= N'',
	@attachments	= N'',
	@codepage		= 0,
	@server 		= 'mail.domain.com'
select RC = @rc 
go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-16 : 13:32:38

i would prefer doing this via sql job which sents the mail periodically rather than in real time. will batch inserts happen to this table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources