February 6, 2011 at 8:43 am
Hi There,
I have a table called Currency with below columns:
CREATE TABLE [dbo].[Currency](
[Currency_ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](50) NULL,
[Rate] [float] NULL,
[DateAdded] [datetime] NULL,
CONSTRAINT [PK_Currency] PRIMARY KEY CLUSTERED
(
[Currency_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Currency] ADD CONSTRAINT [DF_Currency_DateAdded] DEFAULT (getdate()) FOR [DateAdded]
GO
I need to create a trigger on the Currency table if there is a Update, Delete or Insert happening on the table. I have code for a email notification and I want to receive a email if any of the above is happening on the table. Is this possible?
What is the best way to create this trigger?
February 7, 2011 at 12:47 am
Hi here is the solution for the Delete:
CREATE TRIGGER [dbo].[tr_Currency_Del]
ON [dbo].[Currency]
FOR Delete
AS
Email code here
Go
February 7, 2011 at 12:53 am
Refer the article below:
http://www.sql-server-performance.com/articles/dba/dml_triggers_multiple_triggers_p1.aspx
For email notification use the below code in the trigger:
exec msdb..sp_send_dbmail @profile_name = 'Profile Name'
,@recipients ='Email Address'
,@subject = @mail_subject
,@body = @mail_body
,@body_format = 'HTML'
,@importance = 'High'
Hope this helps.
Cheers,
Satnam
February 7, 2011 at 12:56 am
My mistake, actually in SQL 2000 you don't have database mail profile, instead you use xp_send_dbmail
Refer the books online for the exact syntax for the same.
Cheers,
Satnam
February 7, 2011 at 1:04 am
Hi Satnam,
Thanks for your input!
I created the trigger and it is working fine.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply