Trigger Question

[vituja]

CREATE TRIGGER [YearEndProcessing_Mgr] ON [TimeSheet_MGR_TBL]
FOR UPDATE
AS
Hi All,

Still learning triggers. I have a table (timesheet_mgr_Tbl what holds all 7 rows for each employee. Each row has a date fields (mon - fri) for a given week.

I want the trigger to fire when the APPROVED flag ='Y' and if January 1st falls within those seven days.

I need to embed a select statement that will perhaps return a count if it finds a 01/01 date. Any help would be appreicated. Below is my meger trigger which doesn't quite work...


DECLARE @emplid varchar(6)
select @emplid = emplid from updated

DECLARE @DTE varchar(6)
select @DTE = "01/01"/ + year(date) from updated

If (select Approved from updated) = 'Y' and (select date from updated where ) like '01/01%'
and (select emplid from updated) = @emplid
exec YearEnd_Accrual_Vacation @DTE, @emplid
exec YearEnd_Carryover_Vacation @DTE, @emplid
exec YearEnd_Accrual_Personal @DTE, @emplid

 

[nigelrivett]

Do you only cater for single row updates?

If exists (select * from inserted where Approved = 'Y' and right(convert(varchar(8),date,112),4) = '0101')

for multiple rows if @emplid is unique
declare @emplid varchar(6)
select @emplid = ''
while @emplid < (select max emplid from inserted)
begin
select @emplid = min(emplid) from inserted where emplid > @emplid
If exists (select * from inserted where Approved = 'Y' and right(convert(varchar(8),date,112),4) = '0101' and emplid = @emplid)
begin
.....
end
end

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[vituja]

Maybe my whole understanding of triggers need review.

I tried the following code
CREATE TRIGGER javtest2 ON [TimeSheet_Mgr_tbl]
FOR INSERT
AS

BEGIN
if (select date1 from inserted)='01/01/2004'

exec YearEnd_Accrual_Personal '01/01/2004','leelil'
END

but when I insert a new row with a date1 field =&quot;01/01/2004&quot; I get an error &quot;The operation could not be performed because the OLE DB provider 'MSDAORA' does not support distributed transactions.&quot; This is a simple insert trigger check then runs an SP.

 

[nigelrivett]

You are doing something (probably in the SP) that is doing a remote operation on an Oracle database. A trigger always runs within a transaction which is why it is trying to incorporate the remote operation within it.

It is a good idea to keep triggers as simple as possible. Do you really want your insert to fail if the Oracle database is not available? Better to put the remote call into a table and use a scheduled task to run it.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.