Problem in writing a trigger - SQL Server Forums

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)

Problem in writing a trigger

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2008-11-04 : 00:14:19

Hi all,

I am writing a trigger which must update a column in a table based on an if condition.
the trigger will be fired whenever a row is inserted into the project_log table. then it should update the record in the project_task table depending on the date column of project_log table.

the code i have written is like this:

create TRIGGER [dbo].[Trg_timesheet]
ON [dbo].[project_log_time]
AFTER INSERT,UPDATE
AS
BEGIN

if dbo.project_task.act_start_date is null then
UPDATE dbo.project_task
SET act_start_date = (select plt_date from inserted )
end if
if dbo.project_task.act_start_date is not null then
update dbo.project_task
set act_start_date = min(act_start_date,ins.plt_date)
end if

END

here, i want to know where to define the "inserted" table.
i am getting error in the if statement. could someone help me on this?

thanks in advance...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 00:44:40

you're assuming that inserted will contain only a single row which is not always the case. so it should be like below

create TRIGGER [dbo].[Trg_timesheet]
ON [dbo].[project_log_time] 
AFTER INSERT,UPDATE
AS
BEGIN
UPDATE pt
SET pt.act_start_date = 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
FROM dbo.project_task pt
INNER JOIN inserted i
ON i.PK=pt.FK
END

PK is primary key of project_log and FK is column in project_task linked to project_log table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources