I am trying to create a trigger that will set the value of one field based on the values of two other fields. I first try to determine if I'm in an Insert or Update situation, then I make sure one of the two fields I'm interested in has actually changed, then I set calculate the third field's value.
I would appreciate feedback on whether I'm doing this the right way or not. I got most of the ideas from this forum. In addition, I'm currently getting errors "Incorrect syntax near 'TRY' in both locations.
My table structure (partial):
CREATE TABLE [dbo].[PairPalWatch](
[WatchId] [int] IDENTITY(1,1) NOT NULL,
[CheckFrequency] [smallint] NOT NULL DEFAULT (3),
[DateLastChecked] [datetime] NULL,
[DateNextCheck] [datetime] NULL)
And my trigger:
CREATE TRIGGER PairPalWatch_Update_trg
ON PairPalWatch
AFTER UPDATE, INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @DateLastChecked datetime
declare @CheckFrequency smallint
declare @DateLastCheckedOld datetime
declare @CheckFrequencyOld smallint
declare @WatchId int
BEGIN TRY
select @WatchId = ins.WatchId,
@DateLastChecked = ins.DateLastChecked,
@CheckFrequency = ins.CheckFrequency
from INSERTED ins
IF EXISTS(SELECT WatchId FROM deleted)
BEGIN
select @DateLastCheckedOld = del.DateLastChecked,
@CheckFrequencyOld = del.CheckFrequency
from DELETED del
IF ((@DateLastChecked <> @DateLastCheckedOld) or (@CheckFrequency <> @CheckFrequencyOld))
BEGIN
UPDATE PairPalWatch
SET DateNextCheck = CASE @CheckFrequency
WHEN 0 THEN DateAdd(dd , 1 , @DateLastChecked)
WHEN 1 THEN DateAdd(ww , 1 , @DateLastChecked)
WHEN 2 THEN DateAdd(ww , 2 , @DateLastChecked)
WHEN 3 THEN DateAdd(mm , 1 , @DateLastChecked)
ELSE DateAdd(mm , 1 , DateLastChecked) END
WHERE WatchId = @WatchId
END
END
ELSE
BEGIN
UPDATE PairPalWatch
SET DateNextCheck = CASE @CheckFrequency
WHEN 0 THEN DateAdd(dd , 1 , @DateLastChecked)
WHEN 1 THEN DateAdd(ww , 1 , @DateLastChecked)
WHEN 2 THEN DateAdd(ww , 2 , @DateLastChecked)
WHEN 3 THEN DateAdd(mm , 1 , @DateLastChecked)
ELSE DateAdd(mm , 1 , DateLastChecked) END
WHERE WatchId = @WatchId
END
END TRY
BEGIN CATCH
PRINT 'ERROR'
END CATCH
END
GO
CheckFrequency translates to:
0 Daily
1 Weekly
2 BiMonthly (every two weeks)
3 Monthly
ADDED: When I comment out the BEGIN TRY, END TRY and CATCH lines, the Trigger works fine.