April 22, 2007 at 10:37 am
I have an email table that i am building and i would like to update a record when a user replies to an email..
I would like for the trigger to watch for a bit field SENT to be changed from 0 to 1 and when this happens then SET the SENTDATE field to the current datetime.
I need help with someone showing me the correct syntax for this type of update..
Here is what i have so far with the Trigger:
create
TRIGGER Email_Update_Sent
ON
dbo.Email
AFTER
UPDATE
AS
UPDATE
dbo.Email
SET
SentDate = GETDATE()
FROM
dbo.Email
WHERE
dbo.Email.EmailID = dbo.Email.EmailID;
GO
-----------------
Here is my table schema
CREATE
TABLE [dbo].[Email](
[EmailID] [int]
IDENTITY(1312,1) NOT NULL,
[FROM] [varchar]
(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TO] [varchar]
(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Received] [smalldatetime]
NOT NULL CONSTRAINT [DF_Email_Received] DEFAULT (getdate()),
[Attachment] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Read] [bit]
NOT NULL,
[Body] [text]
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Trash] [bit]
NOT NULL,
[Sent] [bit]
NOT NULL,
[SentDate] [smalldatetime]
NULL,
[Saved] [bit]
NOT NULL,
[Inbox] [bit]
NOT NULL,
[Subject] [varchar]
(200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Email] PRIMARY KEY CLUSTERED
(
[EmailID]
ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
thanks
erik
Dam again!
April 22, 2007 at 11:05 am
I believe that this is what i am looking for..
alter
TRIGGER Email_Update_Sent
ON
dbo.Email
AFTER
UPDATE
AS
UPDATE
dbo.Email
SET
SentDate = GETDATE()
FROM
inserted
WHERE
inserted.EmailID = dbo.Email.EmailID;
GO
Dam again!
April 22, 2007 at 11:40 am
Erik
At the moment your trigger will fire whenever any column is updated so I would modify your trigger in one of two ways depending on how you update your table. i.e. Do you issue an update which touches every column regardless of which column has been updated or issuea statement which only effects the SENT column.
In the latter case I would update your trigger as follows:
CREATE
TRIGGER Email_Update_Sent
ON
dbo.Email
AFTER
UPDATE
AS
IF (@@ROWCOUNT = 0)
RETURN;
SET NOCOUNT ON;
IF UPDATE(Sent)
BEGIN
UPDATE dbo.Email
SET SentDate = GETDATE()
FROM inserted
WHERE inserted.EmailID = dbo.Email.EmailID and inserted.Sent = 1 ;
END;
In the former case
CREATE
TRIGGER Email_Update_Sent
ON
dbo.Email
AFTER
UPDATE
AS
IF (@@ROWCOUNT = 0)
RETURN;
SET NOCOUNT ON;
UPDATE dbo.Email
SET SentDate = GETDATE()
FROM inserted
WHERE inserted.EmailID = dbo.Email.EmailID and inserted.Sent = 1
and inserted.Sent <> dbo.Email.Sent;
hth
David
April 22, 2007 at 11:47 am
Hey man thanks
this will help a lot..
cheers!
erik
Dam again!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply