November 9, 2008 at 4:51 am
I'm new to triggers, i successfully made a new trigger that fires upon the change of a column value. On updating multiple rows in a new query using an update statement the trigger is fired only once, i tried to solve it using cursor on the 'inserted' table retrieved by the trigger, but i feel it so complicated, is there any other way to acheive this ??
November 9, 2008 at 5:26 am
Smartdog (11/9/2008)
I'm new to triggers, i successfully made a new trigger that fires upon the change of a column value. On updating multiple rows in a new query using an update statement the trigger is fired only once, i tried to solve it using cursor on the 'inserted' table retrieved by the trigger, but i feel it so complicated, is there any other way to acheive this ??
In the vast majority of the cases, you can write a trigger that will work with multiple rows without the need of cursor. I'm sure that we'll be able to show you how to modify the trigger if you'll post the table's DDL and the trigger's definition.
Also pleas take a look at
http://www.sqlservercentral.com/articles/Best+Practices/61537/. It will only help you getting faster and better answers for your questions.
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/
November 9, 2008 at 5:54 am
the table code:
/****** Object: Table [dbo].[Memo_Tasks] Script Date: 11/09/2008 14:49:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Memo_Tasks](
[task_no] [int] IDENTITY(1,1) NOT NULL,
[memo_code] [nvarchar](10) COLLATE Arabic_CI_AS NULL,
[memo_title] [nvarchar](100) COLLATE Arabic_CI_AS NULL,
[emp_code_sender] [nvarchar](12) COLLATE Arabic_CI_AS NULL,
[emp_code_rec] [nvarchar](12) COLLATE Arabic_CI_AS NULL,
[task_start_date] [smalldatetime] NULL,
[task_end_date] [smalldatetime] NULL,
[task_actual_date] [smalldatetime] NULL,
[importance_code] [nvarchar](1) COLLATE Arabic_CI_AS NULL,
[task_code_finsh] [nvarchar](1) COLLATE Arabic_CI_AS NULL,
[task] [nvarchar](4000) COLLATE Arabic_CI_AS NULL,
[task_percent] [tinyint] NULL,
[task_notce] [nvarchar](4000) COLLATE Arabic_CI_AS NULL,
[kind] [nvarchar](1) COLLATE Arabic_CI_AS NULL,
[memo_code_in_id] [int] NULL,
[tyear] [nvarchar](4) COLLATE Arabic_CI_AS NULL,
[subject_class] [smallint] NULL,
[activity] [int] NULL,
[memo_attach] [nvarchar](4000) COLLATE Arabic_CI_AS NULL,
[ret] [nvarchar](1) COLLATE Arabic_CI_AS NULL,
[met_no] [int] NULL,
[min_no] [nvarchar](2) COLLATE Arabic_CI_AS NULL,
[sector_no] [nvarchar](2) COLLATE Arabic_CI_AS NULL,
[cen_dep] [nvarchar](2) COLLATE Arabic_CI_AS NULL,
[gen_dep] [nvarchar](2) COLLATE Arabic_CI_AS NULL,
[dep] [nvarchar](2) COLLATE Arabic_CI_AS NULL,
[emp_unit] [nvarchar](12) COLLATE Arabic_CI_AS NULL,
[min_task] [nvarchar](1) COLLATE Arabic_CI_AS NULL,
[upload_met] [nvarchar](1) COLLATE Arabic_CI_AS NULL,
[met_place] [nvarchar](1) COLLATE Arabic_CI_AS NULL,
[Note_Tasks] [tinyint] NULL CONSTRAINT [DF_Memo_Tasks_Note_Tasks] DEFAULT ((0)),
[Contact_ID] [int] NULL,
[out_side_ministry] [bit] NULL CONSTRAINT [DF_Memo_Tasks_out_side_ministry] DEFAULT ((0)),
[reader_code] [bit] NULL CONSTRAINT [DF_Memo_Tasks_reader_code] DEFAULT ((0)),
CONSTRAINT [PK_Memo_Tasks] PRIMARY KEY CLUSTERED
(
[task_no] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
the trigger code:
/****** Object: Trigger [OnFinishTask] Script Date: 11/09/2008 14:50:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [OnFinishTask]
ON [dbo].[Memo_Tasks]
AFTER Update
AS
BEGIN
SET NOCOUNT ON;
if update(task_code_finsh)
BEGIN
declare @cursor cursor
declare @EmpCode nvarchar(12)
declare @EndDate smalldatetime
set @cursor=cursor for select emp_code_rec,task_end_date from inserted
open @cursor
fetch next from @cursor into @EmpCode,@EndDate
while @@fetch_status = 0
begin
UPDATE summary
SET FinishedTasks = FinishedTasks + 1,LateTasks=LateTasks-1
WHERE summary.empcode = @empcode and @EndDate<CAST(FLOOR( CAST( GETDATE() AS FLOAT ) )AS DATETIME)
UPDATE summary
SET FinishedTasks = FinishedTasks + 1,CurrentTasks=CurrentTasks-1
WHERE summary.empcode = @empcode and not( @EndDate<CAST(FLOOR( CAST( GETDATE() AS FLOAT ) )AS DATETIME))
fetch next from @cursor into @EmpCode,@EndDate
End
End
END
this is my solution, and sorry um new to this website so i don't know whether um giving enough info or not, plz be patient with me 🙂
November 9, 2008 at 6:20 am
Smartdog (11/9/2008)
the table code:/****** Object: Table [dbo].[Memo_Tasks] Script Date: 11/09/2008 14:49:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Memo_Tasks](
[task_no] [int] IDENTITY(1,1) NOT NULL,
[memo_code] [nvarchar](10) COLLATE Arabic_CI_AS NULL,
[memo_title] [nvarchar](100) COLLATE Arabic_CI_AS NULL,
[emp_code_sender] [nvarchar](12) COLLATE Arabic_CI_AS NULL,
[emp_code_rec] [nvarchar](12) COLLATE Arabic_CI_AS NULL,
[task_start_date] [smalldatetime] NULL,
[task_end_date] [smalldatetime] NULL,
[task_actual_date] [smalldatetime] NULL,
[importance_code] [nvarchar](1) COLLATE Arabic_CI_AS NULL,
[task_code_finsh] [nvarchar](1) COLLATE Arabic_CI_AS NULL,
[task] [nvarchar](4000) COLLATE Arabic_CI_AS NULL,
[task_percent] [tinyint] NULL,
[task_notce] [nvarchar](4000) COLLATE Arabic_CI_AS NULL,
[kind] [nvarchar](1) COLLATE Arabic_CI_AS NULL,
[memo_code_in_id] [int] NULL,
[tyear] [nvarchar](4) COLLATE Arabic_CI_AS NULL,
[subject_class] [smallint] NULL,
[activity] [int] NULL,
[memo_attach] [nvarchar](4000) COLLATE Arabic_CI_AS NULL,
[ret] [nvarchar](1) COLLATE Arabic_CI_AS NULL,
[met_no] [int] NULL,
[min_no] [nvarchar](2) COLLATE Arabic_CI_AS NULL,
[sector_no] [nvarchar](2) COLLATE Arabic_CI_AS NULL,
[cen_dep] [nvarchar](2) COLLATE Arabic_CI_AS NULL,
[gen_dep] [nvarchar](2) COLLATE Arabic_CI_AS NULL,
[dep] [nvarchar](2) COLLATE Arabic_CI_AS NULL,
[emp_unit] [nvarchar](12) COLLATE Arabic_CI_AS NULL,
[min_task] [nvarchar](1) COLLATE Arabic_CI_AS NULL,
[upload_met] [nvarchar](1) COLLATE Arabic_CI_AS NULL,
[met_place] [nvarchar](1) COLLATE Arabic_CI_AS NULL,
[Note_Tasks] [tinyint] NULL CONSTRAINT [DF_Memo_Tasks_Note_Tasks] DEFAULT ((0)),
[Contact_ID] [int] NULL,
[out_side_ministry] [bit] NULL CONSTRAINT [DF_Memo_Tasks_out_side_ministry] DEFAULT ((0)),
[reader_code] [bit] NULL CONSTRAINT [DF_Memo_Tasks_reader_code] DEFAULT ((0)),
CONSTRAINT [PK_Memo_Tasks] PRIMARY KEY CLUSTERED
(
[task_no] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
the trigger code:
/****** Object: Trigger [OnFinishTask] Script Date: 11/09/2008 14:50:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [OnFinishTask]
ON [dbo].[Memo_Tasks]
AFTER Update
AS
BEGIN
SET NOCOUNT ON;
if update(task_code_finsh)
BEGIN
declare @cursor cursor
declare @EmpCode nvarchar(12)
declare @EndDate smalldatetime
set @cursor=cursor for select emp_code_rec,task_end_date from inserted
open @cursor
fetch next from @cursor into @EmpCode,@EndDate
while @@fetch_status = 0
begin
UPDATE summary
SET FinishedTasks = FinishedTasks + 1,LateTasks=LateTasks-1
WHERE summary.empcode = @empcode and @EndDate<CAST(FLOOR( CAST( GETDATE() AS FLOAT ) )AS DATETIME)
UPDATE summary
SET FinishedTasks = FinishedTasks + 1,CurrentTasks=CurrentTasks-1
WHERE summary.empcode = @empcode and not( @EndDate<CAST(FLOOR( CAST( GETDATE() AS FLOAT ) )AS DATETIME))
fetch next from @cursor into @EmpCode,@EndDate
End
End
END
this is my solution, and sorry um new to this website so i don't know whether um giving enough info or not, plz be patient with me 🙂
You can modify the trigger so it will include only one update statement with no cursor at all. Here is my version of your trigger (notice it is untested so you should test it!):
CREATE TRIGGER [OnFinishTask]
ON [dbo].[Memo_Tasks]
AFTER Update
AS
BEGIN
SET NOCOUNT ON;
if update(task_code_finsh)
BEGIN
update summery
set FinisedTasks = FinishedTaskes + 1,
LateTasks = case when i.EndDate <CAST(FLOOR( CAST( GETDATE() AS FLOAT ) )AS DATETIME) then LateTasks - 1
else LateTasks end,
CurrentTasks = case when i.EndDate >= CAST(FLOOR( CAST( GETDATE() AS FLOAT ) )AS DATETIME)then CurrentTasks -1
else CurrentTasks end
from summery inner join inserted i on s.empcode = i.empcode
End --if update
END
In the trigger I'm using update statement with a join to inserted view. This is why we don't need to cursor. The case statements make sure that each time I'll update the column that I need to update (or at least this is what I think:-)).
As for the information that you gave - this was the exact information that was needed:-).
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/
November 9, 2008 at 6:54 am
Thx for the great way of 'case when else' but my problem was in the fact that this trigger fires only once when the update statement is called, so let the query:
update memo_tasks set task_code_finish = 1 where emp_code_rec = '0101'
it's supposed to run the trigger by the number of records affected by the statement so as to reflect in the table summary:
/****** Object: Table [dbo].[Summary] Script Date: 11/09/2008 15:53:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Summary](
[EmpCode] [nchar](12) COLLATE Arabic_CI_AS NULL,
[UnreadMemos] [int] NULL CONSTRAINT [DF_Summary_UnreadMails] DEFAULT ((0)),
[ReadMemos] [int] NULL CONSTRAINT [DF_Summary_ReadMails] DEFAULT ((0)),
[SentMemos] [int] NULL CONSTRAINT [DF_Summary_SentMails] DEFAULT ((0)),
[UnsentMemos] [int] NULL CONSTRAINT [DF_Summary_UnsentMails] DEFAULT ((0)),
[NewTasks] [int] NULL CONSTRAINT [DF_Summary_NewTasks] DEFAULT ((0)),
[LateTasks] [int] NULL CONSTRAINT [DF_Summary_OldTasks] DEFAULT ((0)),
[FinishedTasks] [int] NULL CONSTRAINT [DF_Summary_FinishedTasks] DEFAULT ((0)),
[CurrentTasks] [int] NULL CONSTRAINT [DF_Summary_CurrentTasks] DEFAULT ((0)),
[TodayTasks] [int] NULL CONSTRAINT [DF_Summary_TodayTasks] DEFAULT ((0))
) ON [PRIMARY]
but in fact the trigger is fired only once !!!
i read in a forum that a trigger is fired upon TABLE update not the record update, so all i want to know if the scope of the trigger is implrtant or not, and any suggested solution for my case,
thx 🙂
November 9, 2008 at 7:13 am
Notice that in the update statement I'm joining summery table to inserted views. This should make should update all the records in summery table, that were also updated in the original table.
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/
November 9, 2008 at 7:25 am
sorry you got me wrong
um updating a record in a row in the table summary by adding the number of rows the been updated into the existing number
let for the employee '0101' in summary table i have FinishedTasks = 12
then after the update statment that affects 10 records in table memo_tasks the 12 should be 22
and that would not happen except if the trigger is fired 10 times by the number of records updated, and that's why i used the cursor in order to call the code inside trigger for several times
November 9, 2008 at 1:44 pm
Smartdog (11/9/2008)
sorry you got me wrongum updating a record in a row in the table summary by adding the number of rows the been updated into the existing number
let for the employee '0101' in summary table i have FinishedTasks = 12
then after the update statment that affects 10 records in table memo_tasks the 12 should be 22
and that would not happen except if the trigger is fired 10 times by the number of records updated, and that's why i used the cursor in order to call the code inside trigger for several times
I hope that I understood you correctly this time. If I did, then there is a way to do it without a cursor. Can you check this version of the trigger (again notice that I didn't test it):
CREATE TRIGGER [OnFinishTask]
ON [dbo].[Memo_Tasks]
AFTER Update
AS
BEGIN
SET NOCOUNT ON;
if update(task_code_finsh)
BEGIN
WITH CountTasks as (
select CountOfFinishedTasks = sum(case when i.task_end_date <CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME)
then 1 else 0 end),
CountOfCurrentTasks = sum(case when i.task_end_date <CAST(FLOOR(CAST(GETDATE() AS FLOAT ))AS DATETIME)
then 0 else 1 end),
emp_code_rec,
case when i.task_end_date <CAST(FLOOR(CAST(GETDATE()AS FLOAT))AS DATETIME) then 1 else 0 end as EndDateFlag
from inserted i
group by emp_code_rec,
case when i.task_end_date <CAST(FLOOR( CAST( GETDATE() AS FLOAT ) )AS DATETIME) then 1 else 0 end)
update summery
set FinisedTasks = FinishedTaskes + CountOfCurrentTasks + CountOfFinishedTasks,
LateTasks = case when EndDateFlag = 1 then LateTasks - CountOfFinishedTasks
else LateTasks end,
CurrentTasks = case when EndDateFlag = 0 then CurrentTasks - CountOfCurrentTasks
else CurrentTasks end
from summery inner join CountTasks i on s.emp_code_rec = i.emp_code_rec
End --if update
END
--------------------------------------------------------------
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/
November 10, 2008 at 12:47 am
Thanks a lot for ur help, i thnk you got me right this time,
i just have one question, what is difference between row trigger and tabl trigger, and what kind of triggers is that one ??
November 10, 2008 at 1:08 am
Smartdog (11/10/2008)
Thanks a lot for ur help, i thnk you got me right this time,i just have one question, what is difference between row trigger and tabl trigger, and what kind of triggers is that one ??
While I admit that I’m not completely sure about the terms row trigger and table trigger, I think I understand what you are talking about. With Oracle you can create a trigger that will fire for each row modification, so when you issue a single update statement that modified 20 rows, the trigger will fire 20 times and not just once. If no row was affected, the trigger will not run even once. My guess is that this is a row trigger. SQL Server doesn’t have this kind of trigger. With SQL Server each statement fires the trigger exactly once regardless of the number of rows that was affected by the statement.
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/
November 10, 2008 at 1:14 am
Adi Cohn (11/10/2008)
Smartdog (11/10/2008)
Thanks a lot for ur help, i thnk you got me right this time,i just have one question, what is difference between row trigger and tabl trigger, and what kind of triggers is that one ??
While I admit that I’m not completely sure about the terms row trigger and table trigger, I think I understand what you are talking about. With Oracle you can create a trigger that will fire for each row modification, so when you issue a single update statement that modified 20 rows, the trigger will fire 20 times and not just once. If no row was affected, the trigger will not run even once. My guess is that this is a row trigger. SQL Server doesn’t have this kind of trigger. With SQL Server each statement fires the trigger exactly once regardless of the number of rows that was affected by the statement.
Adi
Thx a lot Adi Cohn, enough info to Destroy my team leader 😀
November 10, 2008 at 5:19 am
hi,
i had the same problem. sql server trigger does not affect all rows when i updated more than one row a time.
what i do now is to run a job every 5 min (instead of the trigger) with a cursor, as you have already implemented.
does the idea from adi work?
good luck,
sue
Susanne
November 10, 2008 at 5:34 am
kuka_99 (11/10/2008)
hi,i had the same problem. sql server trigger does not affect all rows when i updated more than one row a time.
what i do now is to run a job every 5 min (instead of the trigger) with a cursor, as you have already implemented.
does the idea from adi work?
good luck,
sue
hey kuka,
i didn't try adi's method because i found out that it was not what i want,
and the fact that the trigger run once per update statement (even if this update statement affects more rows) will cause the trigger to fire once, and my method was to use cursor on the 'inserted' table that i can use within the trigger, and this table contains all the records affected due to the update, so u can use ur cursor in the trigger instead of this job, and the trigger i posed by the beginin' of the thread shows how to use the cursor inside the trigger,
good luck
November 10, 2008 at 8:10 pm
Smartdog (11/10/2008)
kuka_99 (11/10/2008)
hi,i had the same problem. sql server trigger does not affect all rows when i updated more than one row a time.
what i do now is to run a job every 5 min (instead of the trigger) with a cursor, as you have already implemented.
does the idea from adi work?
good luck,
sue
hey kuka,
i didn't try adi's method because i found out that it was not what i want,
and the fact that the trigger run once per update statement (even if this update statement affects more rows) will cause the trigger to fire once, and my method was to use cursor on the 'inserted' table that i can use within the trigger, and this table contains all the records affected due to the update, so u can use ur cursor in the trigger instead of this job, and the trigger i posed by the beginin' of the thread shows how to use the cursor inside the trigger,
good luck
You do not need a cursor or a job to accomplish this task.
The fact that the trigger is called only once, does not mean that you can not access all the data affected by a given query.
It seems that you are not understanding how to properly use triggers.
The following article explains how to create set-based triggers. I recommend reading it.
http://www.sqlservercentral.com/articles/Triggers/64214/
Also, if you are using SQL Server 2005, the OUPUT operator may help you.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply