March 16, 2009 at 5:50 am
Hi,
I am writing t-sql Trigger ,and i am firing it on update here my problem is it is getting fired on each Row update, i want to fire my Procedure when the whole process of updation completes on particular Table,
like it happens in Oracle....!!
Is there any work around for this???
PS: i have Tried is of After Trigger also but .....:(
Thanks
Parth
March 16, 2009 at 6:25 am
instead of on update use after update
Raj Acharya
March 16, 2009 at 6:31 am
Hi, Raj
if you will see i have already tried it in my Trigger but of no use.
Thanks anyways.
Parth
March 16, 2009 at 6:50 am
hi if you can give more description abt what's your update statement ....
and what you have written in trigger
and r u using row that are updated in procedure... give some description or example something so i can help
Raj Acharya
March 16, 2009 at 6:57 am
Hi,
basically i am firing mails using DB,
and its getting fired with every Row Level Update.
Thanks
Parth
March 16, 2009 at 7:13 am
Triggers don’t get fired for each record, they get fired for each statement. This means that if you ran one update statement, the trigger will be executed exactly one time regardless of the number of records that were effected. The code bellow proves it. If you want more help, pleas provide us more details (check out the link in my signature to see how to post question and increase the speed and accuracy of the answers).
use tempdb
go
--Creating the table
--and inserting data to it
create table DemoTable (i int)
go
insert into DemoTable (i)
select 1 union select 2 union select 3
go
--Creating the log table
create table LogTable (vc varchar(40))
go
--Creating the trigger on update
create trigger tr_update_DemoTable
on DemoTable
for update
as
insert into LogTable (vc) values ('trigger ran')
go
update DemoTable set i = i * 10
go
--If the trigger ran once, I'll see one record
--in LogTable. If it ran 3 times because there
--are 3 recods in DemoTable, I'll see 3 records
select * from LogTable
go
--cleanup
drop table DemoTable
drop table LogTable
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 16, 2009 at 7:28 am
I understood, but i am using this Trigger to fire my mail, which i need to fire after all the updation takes place on the table then and then only all the Details should get fire in the Mail. here in my case it happens that it is getting fire up on the each updation and unfortunatally i cant change the way updation is happening and it is happening for each row.
Any help you can do. Please let me know.
Thanks
Parth
March 16, 2009 at 7:33 am
the virtual table INSERTED, which exists only for the duration of the trigger, has all the records that were updated.
if you show us your current trigger in SQL, we can help add a loop that would call your email for every row in INSERTED.
generally i like to put all emails in a separate table, and have a scheduled job roll thru that table every minute or so, but since sp_send_dbmail is asyncronous, you can do it in your trigger instead
Lowell
March 16, 2009 at 7:43 am
parth83.rawal (3/16/2009)
I understood, but i am using this Trigger to fire my mail, which i need to fire after all the updation takes place on the table then and then only all the Details should get fire in the Mail. here in my case it happens that it is getting fire up on the each updation and unfortunatally i cant change the way updation is happening and it is happening for each row.Any help you can do. Please let me know.
Thanks
Parth
Actually it seems to me that you don’t understand (or I don't understand you). We are all saying that the triggers in SQL Server run exactly one time regardless of the number of records that were effected by the trigger. I wrote a small demo that shows it, but you insist that your trigger is running after each record is getting update and not when the update operation finished updating all the records. You also insist on not showing us your code, which makes it impossible or very hard to help you. Can you at least explain why you think that we are all wrong and why you won’t show your code?
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 16, 2009 at 7:48 am
i am keep saying that here it is getting fire after each Update basically it is updating Row by Row from another Windows Prog. and it is getting reflected in the Database below is the code for it,
Thanks for barring with me...what a dumbo i am.
ALTER TRIGGER TR_RES_NEW_ENTRY ON MSP_EpmAssignment AFTER UPDATE
AS
BEGIN
DECLARE
@STR_RESUID UNIQUEIDENTIFIER,
@PROJ_UID UNIQUEIDENTIFIER,
@STR_MAIL NVARCHAR(MAX),
@STR_PROJ_OWNER NVARCHAR(100),
@tableHTML NVARCHAR(MAX),@tableHTML1 NVARCHAR(MAX),
@STR_PROJ_NAME NVARCHAR(MAX), @STR_COUNT INT,@STR_Sub NVarchar(200);
SET @tableHTML1=' '
IF UPDATE(ResourceUID)
BEGIN
delete debug
--select * from debug
--create table #new (FL1 uniqueidentifier)
--output inserted.AssignmentUID into #new
--insert into Debug select Inserted.AssignmentUID from Inserted
Select @STR_RESUID=Ins.ResourceUID from Inserted Ins
select @PROJ_UID=Ins.ProjectUID from Inserted Ins
--PRINT @STR_RESUID
SELECT @STR_PROJ_NAME=PROJ_NAME from [ProjectServer_Published].dbo.msp_ProjectS P,Inserted Ins
Where Ins.ProjectUID=P.proj_uid
and P.proj_uid=@PROJ_UID
SELECT @STR_PROJ_OWNER=RES_NAME From [ProjectServer_Published].dbo.msp_ProjectS P,Inserted Ins,
[ProjectServer_Published].dbo.Msp_ResourceS R
Where R.RES_UID=P.WRES_UID and Ins.ProjectUID=P.proj_uid
and Ins.ResourceUID=R.res_UID
and P.proj_uid=@PROJ_UID
SELECT @STR_MAIL=isnull(WRES_EMAIL,'xyz@abc.com') FROM [ProjectServer_Published].[dbo].[Msp_Resources] WHERE RES_UID=@STR_RESUID;
SELECT @STR_COUNT=COUNT(*) from
[ProjectServer_Published].dbo.Msp_TasksT,
[ProjectServer_Published].dbo.MsP_Assignments A,
[ProjectServer_Published].dbo.Msp_Projects P,
[ProjectServer_Published].dbo.Msp_Resources R
Where A.RES_UID=R.RES_UID and A.PROJ_UID=P.PROJ_UID and A.Task_UID=T.Task_UID and
P.PROJ_UID=T.PROJ_UID and Task_IS_Critical=1 and TASK_PCT_COMP<>100
--and {fn Curdate()}=TaskBaseline0FinishDate-3
and A.ASSN_UID in (select Inserted.AssignmentUID from Inserted)
PRINT @STR_COUNT
print @STR_PROJ_NAME
PRINT @STR_RESUID
print @STR_MAIL
--Delete Debug99
Print 'Hermionie Reached: Debug99'
--insert into debug99 values(@STR_COUNT)
BEGIN
SET @tableHTML = N' '+
N'
<!--
.style3 {font-size: 10px}
.style5 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; font-weight: bold; color: #FFFFFF; }
.style6 {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 12px;
}
.style19 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; font-weight: bold; }
.style7{font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; }
-->
'+
N' ' +
N' ' +
N' '+
N' '+
N' '+
N' ' +
N' '+
N' '+
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
N' '+
CAST ( ( SELECT
td=Row_Number() over( Order by Task_WinProj_Unique_ID),'',
td = [ProjectServer_Reporting].dbo.Fn_Task_Wbs(P.Proj_UID,convert(char,Task_WBS)), '' ,
td=T.[Task_Name],'',
td = CONVERT(VARCHAR,[Task_Start_Date],6), '' ,
td = CONVERT(VARCHAR,[Task_Finish_Date],6), '',
td = [Task_PCT_COMP], ''
from
[ProjectServer_Published].dbo.Msp_TasksT,
[ProjectServer_Published].dbo.MsP_Assignments A,
[ProjectServer_Published].dbo.Msp_Projects P,
[ProjectServer_Published].dbo.Msp_Resources R
Where A.RES_UID=R.RES_UID and A.PROJ_UID=P.PROJ_UID and A.Task_UID=T.Task_UID and
P.PROJ_UID=T.PROJ_UID and Task_IS_Critical=1 and TASK_PCT_COMP<>100
and A.ASSN_UID in (select Inserted.AssignmentUID from Inserted)
Order by Task_WinProj_Unique_ID
FOR XML PATH( 'tr'), TYPE ) AS NVARCHAR( MAX) ) +
N' ' ;
SET @tableHTML1 = N' '+
N' '+
N' '+
N' ' +
N' ';
SET @tableHTML=@tableHTML+ @tableHTML1;
IF @STR_COUNT <>0
Begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name=N'Admin',
@recipients=@STR_MAIL,
@subject =@STR_Sub,
@body = @tableHTML,
@body_format = 'HTML' ;
End
end
--Print 'Nishant Idea:--'+isnull(@tableHTML,'Herminoe')
--SET @STR_Sub='Re Assignment ['+@STR_PROJ_NAME+']'
--Print 'Hermionie Reached: Debug99 insert data'
--insert into debug99 values(@tableHTML)
End
End
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
March 16, 2009 at 8:12 am
I think that I understand you now. Instead of using one update statement that updates all the records, you are looping through the records and updating each record with an update statement. Since each record is updated with its own update statement, the trigger is running for each statement. If this is the case, then this is the way that triggers work. You won’t be able to change it. You’ll need to redesign your code. The first thing that I would check is if it is really necessary to loop through the records and update each one of them independently. The second thing that I’d change is the trigger. I would insert the information that should be sent by db_mail into a different table in the trigger instead of sending the mail from the trigger (just like it was suggested earlier). After you finish the whole process you can decide if you want to send the mail or not.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 16, 2009 at 8:32 am
Thank a ton
i guess now we are all on the same page, here i dont have the privilage to change the code and that is what i cant do,
what i do is i am trying exactly what you just said is of no use. it is just giving me the 1 record each time.....:(
in the current situation can i change some thing to incorporate my Req.
any help would me much appreciated.
i guess there must be some way to achieve this , if i will store some data how do my mailing Proc will understand that it should run. untill i will place it inside the JOB, but again then i have to change the Table where i can change the Records , Please please let me know if anything clicks you
Thanks
Parth
March 16, 2009 at 8:37 am
Edited your code so it would better fit on the page.
A couple of things:
- sending mail in a trigger is a bad idea. It's part of the transaction, and if mail is slow, you'll block the table.
- The suggested solution is to put the data in a table from the trigger and then have a process (could be a SQL job running a stored proc) to read through, grab new data, send the mail, and mark things as sent.
- updation isn't a word, btw.
Can you spell out the requirements clearly here that you're trying to achieve? If one or ten users update a row from their program, what should happen? What if a user updates 10 rows?
March 16, 2009 at 8:47 am
Thanks for the advice i guess i will run this trigger inside JOB,
actually i have tried explaining this to my team but untill no body stuck no one believes...:)
Anyways i get my doubts clear, and i will not put this mail inside the trigger what i will do is:
Store Inserted data to Table ,
Proc will fetch the data and Sends the mails to the users.
and after wards i will Delete the data from the table.
can you tell about Excepton hanling inside this mailing Proc.???
that i can do.
Thanks for the Help.
Parth
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply