December 9, 2009 at 11:04 am
To follow up on what Gail said, (and make it very obvious)
Declare @NewBody Varchar(100)
Set @NewBody = 'A record with id ' + d.id + ' has been updated'
--Note: Where is this d.id coming from?
/*Change the Following:
-- Send email Regarding this transaction
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMailProfile',
@recipients = 'recipient@gmail.com',
@body = 'A record with id ' + d.id + ' has been updated',
@subject = 'Subject: Database Mail'
--To the Following
*/
-- Send email Regarding this transaction
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMailProfile',
@recipients = 'recipient@gmail.com',
@body = @NewBody,
@subject = 'Subject: Database Mail'
But you will continue to get an error until you resolve the issue with d.id.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 9, 2009 at 11:06 am
CirquedeSQLeil (12/9/2009)
To follow up on what Gail said, (and make it very obvious)...
But you will continue to get an error until you resolve the issue with d.id.
My guess, from the INSERTED table since it is aliased as "d".
December 9, 2009 at 11:14 am
Yes, you are right. I didnt' know that. I moved the @body and it's now working since it has access to d.id
December 9, 2009 at 11:55 am
FROM
INSERTED D
set @NewBody = 'A record with id ' + @@IDENTITY + ' has been updated.'
Actuall, d.id should come from the temporary inserted table. since it's automatically set to grow i thought i use @@identity.
Please suggest. The trigger is now stored in the triggers folder but when I add a record in the table i get following error.
No row was updated.
The data in row 18997 was not committed.
Error source: .net sqlclient data provider.
error message: the transaction ended in the trigger. the batch has been aborted.
correct the errors and retry or press esc to cancel the change(s).
December 9, 2009 at 12:01 pm
Repost the code for your trigger. Since you have made changes we really need to see what you are actually running.
December 9, 2009 at 12:42 pm
DECLARE @tID int
SET @tID = (SELECT ID FROM inserted)
I added this before the insert statement and bingo it worked.
Thanks for all your help.
December 9, 2009 at 12:49 pm
Post the code for your trigger. Since you have made changes, it would be helpful to see the new code.
December 9, 2009 at 12:51 pm
Lynn,
It's now working. However, it was suggested i used join instead of subquery.
select se.desc from se where (se.id = d.seid)) as seDesc . how would you do it?
I am re-formatting the code and will post it shortly as I can't display the actual tables and fields.
December 9, 2009 at 12:52 pm
doran_doran (12/9/2009)
DECLARE @tID intSET @tID = (SELECT ID FROM inserted)
What happens if there's more than 1 row in inserted?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2009 at 12:59 pm
As I understood reading from different blogs that Trigger only handles one row at a time.
If not then I guess I have to declare an array and then use do while loop.
December 9, 2009 at 1:06 pm
doran_doran (12/9/2009)
As I understood reading from different blogs that Trigger only handles one row at a time.
No. Triggers in SQL are statement triggers. Fire once per operation and there are as many rows in the inserted/deleted tables as there were rows modified by the operation that fired the trigger.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2009 at 1:10 pm
In SQL Server, triggers are fired once regardless if it is one row or multiple rows. You need to write triggers to handle both situations.
December 9, 2009 at 1:24 pm
1. Okay. Now I need a help big time. How do I handle if there are multiple updates or inserts?
2. I declared some variables before the try block. Are they not visible by try block.
December 9, 2009 at 1:38 pm
Post. The. Code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2009 at 2:12 pm
USE [myDATABASE]
GO
/****** Object: Trigger [dbo].[it_myTrigger] Script Date: 12/09/2009 13:35:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[it_myTrigger]
ON [dbo].[Dodge]
FOR INSERT
AS
DECLARE@tTable varchar(30),
@tID int, -- New Dodge ID from Dodge Table
@newID varchar(20), -- Converts DodgeID to string
@NewBody varchar(500), -- Body of the email
@runDateTime datetime, -- Date and Time this trigger was executed
@UserName varchar(100), -- Machine user that this is running under.
@thisError varchar(100), -- Error number to log it in log table
@thisErrorMsg varchar(100) -- Error Message to log it in log table
SET @runDateTime = GETDATE()
SET @UserName = system_user
SET @tID = (select Dodgeid from inserted)
set @newID = @tid
set @NewBody = 'New record with id ' + @newid + ' has been created in ' + @ttable
set @tTable = 'Dodge'
BEGIN TRY
set identity_insert [myDATABASE].[dbo].[Dodge] on
-- INSERTING RECORD INTO ANOTHER DATABASE TABLE
INSERT INTO [LogDataBase].[dbo].[TARGET_TABLE](
LID,
LName,
FName,
MName,
NSuffix,
DOB,
Gender,
DEPARTMENT,
ETHNC,
DOH,
DOT,
AFTERSTATUS,
DOR,
BEFORESTATUS,
CURRENTSTATUS)
SELECT
d.Dodgeid,
d.lname,
d.fname,
d.mname,
(Select n.DESC from dbo.suffix as n where (n.id = d.NSid)) as NS,
d.DOB,
d.GNDR,
(select s.DESC from s where (s.id = d.sid)) as sDesc,
(select se.DESC from se where (se.id = d.seid)) as seDesc,
(select sc.Desc from sc where (sc.id = d.scid)) as scdesc,
(select r.Desc from rc where (rc.id = d.rcid)) as rcDesc,
d.dot,
(select p.DESC from p where (p.id = d.pid)) as pDesc,
d.dor,
mDesc = (select m.DESC from m where (m.id = d.mid)),
aDesc = (select a.DESC from a where (a.id = d.id))
FROM
INSERTED D
SET IDENTITY_INSERT [myDATABASE].[DBO].[Dodge] OFF
-- Send email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMailProfile',
@recipients = 'george@gmail.com',
@body = @NewBody, -- BODY IS blank cause it's not reading from the variables declared above
@subject = 'Subject: New Record is added in ' --+ @tTable -- this line gives me error cause it's not reading
END TRY
BEGIN CATCH
-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
BEGIN
ROLLBACK -- ROLLBACK THE TRANSACTION
END
SET @thisError = error_number()
SET @thisErrorMsg = error_message()
INSERT INTO [LogDataBase].[dbo].[logs]
(username,
logdatetime,
tablename,
useraction,
COMMENT)
VALUES (@UserName,
@runDateTime,
'TARGET_TABLE',
'Trigger Insert',
'Attempt Failed')
-- loggin actual error from sql server
INSERT INTO [LogDataBase].[dbo].[logs]
(username,
logdatetime,
tablename,
useraction,
COMMENT)
VALUES (@UserName,
@runDateTime,
'TARGET_TABLE',
'Insert',
@thiserror + ' ' + @thiserrormsg)
END CATCH
--Log emails into LogDataBase Log table
INSERT INTO [LogDataBase].[dbo].[logs]
(username,
logdatetime,
tablename,
useraction,
COMMENT)
VALUES (@UserName,
@runDateTime,
@ttable,
'e-mail',
@newBody)
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply