How to use if condition in a trigger

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)

How to use if condition in a trigger

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2008-11-13 : 05:07:45

Hi,
In the following trigger,i want to give the commented lines as condition. pls suggest me how to do tht
**********************************************************************
create trigger timesheet
on [dbo].[project_log_time]
after insert,update
as
begin
/* when there are no rows then*/
update pt set act_date = case when cond 1 then task1
else when cond2 then task2
end
/*when there are rows then*/
update pt set act_date = case when cond 1 then task1
else when cond2 then task2
end
end

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-13 : 05:14:23

check the inserted pseudo table.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

mrm23
Posting Yak Master

198 Posts

Posted - 2008-11-13 : 05:48:36

then should i declare tht date as variable ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-13 : 05:51:25

what do you mean by "when there are no rows"? when there are no rows where?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

mrm23
Posting Yak Master

198 Posts

Posted - 2008-11-13 : 06:10:16

the trigger is written on project_log_time table. in this table entries a made against a task.
the tasks are listed in project_task table.
now,the condition is like:
"when there are no rows for a particular task in the project_log_time table, then update...."

the actual trigger is like this:

ALTER TRIGGER [dbo].[Trg_timesheet]
ON [dbo].[PROJECT_LOG_TIME]
AFTER INSERT,UPDATE
AS
BEGIN
DECLARE
@plt_object INT
SET @plt_object = SELECT plt_object FROM project_log_time
declare @date datetime
set @date = select min(plt_date) from project_log_time
BEGIN
UPDATE pt
SET pt.act_start_date =
case when count(@plt_object) < 1 then

CASE WHEN pt.act_start_date IS NULL
THEN i.plt_date
ELSE CASE WHEN pt.act_start_date>i.plt_date
THEN i.plt_date
ELSE pt.act_start_date
END
END
case when count(@plt_object) > 1 then
update pt
set pt.act_start_date = case when pt.act_start_date is null
then min(plt_date)
else case when pt.act_start_date > i.plt_date
then min(@date,pt.act_start_date)
end
end

FROM dbo.project_task pt
INNER JOIN inserted i
ON i.plt_object=pt.ptk_seq_no
END

let me know what is wrong with this

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-13 : 06:21:12

are you sure you're supposed to be doing this in a trigger??? it makes no sense at all

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources