How do I Trigger on particular rows based on date periods. 1

[Scagman]

Sql 7.0 on w2000

I want to be notified when a value is changed in a column but only for particular rows....

eg. the pertinent data is

Field Name Type Value
---------- ----- -------
Date1 Smalldatetime dd/mm/yy
field1 smallint 0 or -1
Field2 bit 0 or 1

I want the trigger to fire only when Field1 or Field2 is updated
for
any row that has a date of the current day + 7 days.

Two scenarios , mail one person when field2 changes to 1 and mail
someone else when it changes to 0......

I would also like to send the value of the row in the email

I have written only one other trigger and am lost....

Heeelp please....

Lester (lester.dale@compaq.com)

 

[tlbroadbent]

Triggers fire when a ROW is updated (or inserted or deleted) not when specific columns are updated. You can test in q query if a column has been updated using IF Updated(Column) or the COLUMNS_UPDATED function.

The topic "Create Trigger" in SQL Books Online contains information that about these capabilities. There is also an example of using xp_sendmail in a trigger. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Scagman]

Yep, I appreciate that info about the particular columns being updated and I have seen it in the manual, but I still need to evaluate the value of the date field in the row that the trigger is reporting a change on. I presume part of the code would look like this...I'm having trouble with the right syntax to further check the date value...

CREATE TRIGGER TriggerCarparkchange
ON Activities
FOR Update , Insert AS
IF UPDATE(Field1) OR UPDATE(Field2)
Begin
Some SQL if syntax ......
Where ([Date1] <= DATEADD(day, 7, GETDATE())) AND ([Date1] >= GETDATE()) AND (Field1 = - 1).....

EXEC master..xp_sendmail 'mail id', 'relevant stuff'
End


Thanks in advance....Lester

 

[tlbroadbent]

Here is an untested example.

CREATE TRIGGER TriggerCarparkchange
ON Activities
FOR Update , Insert AS

IF UPDATE(Field1) OR UPDATE(Field2)

Begin

Declare @date datetime, @fld1 int, @fld2 int
Declare @rcp varchar(30), @sbj varchar(60)
Declare @msg varchar(200)

Declare @today datetime
Set @today = convert(char(11),Getdate())

Select @date=[Date1], @fld1=[Field1], @fld2=[Field2]
From Inserted

If (@date Between @today And Dateadd(dd,7,@today))
And @f2 = 1
Set @rcp='Mary Jones'
Else
Set @rcp='John Smith'

Set @sbj='This is a subject'
Set @msg='The record has changed. ' +
'Date1=' + convert(varchar(11),@date) +
' Field1=' + @fld1 +
' Field2=' + @fld2

exec master.dbo.xp_sendmail
@recipients=@rcp,
@subject=@sbj,
@message=@msg

End Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.