Checking User & Expire of Update Possibility (by Trigger), How to?

Hi,

I have Table (RatesTable) every user can insert records to this table, and all users can see this records, this table contain the following columns:

RateID, Service, Rate, DateTime, User

Want I want is a code (trigger) in the database can do the following:

If user perform an Update request the code will check:

- if this recored inserted by the same user update command will be execute.

- if this recored inserted by other user: update command will not execute and return message.

- if more than 5 minutes passed the update command will not be execute and return message.

Yes, this can be done with a trigger but it really would be better for the update statement itself to decide whether or not the update is allowed by adding either a WHERE condition or an AND condition to the update statement to decide whether or not to allow the update.

Change the update statement from something like:

update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId

to something like

update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
and user = @.currentUser
and dateTime >= dateadd (mi, -5, getdate())

|||

Thanks Kent Waldrop Ap07, but the problem is my all program use Datasets created by Data Source Configration Wizzard, is it possible to to add your code to the Dataset Designer?

and what if I want to put this code in Trigger?

thnx again,,,

|||Any help?|||

Here is a TRIGGER idea. While I agree with Kent that changing the UPDATE statement is a better option, I know from expereince that it is not always the solution that works.

The code idea below relies upon [User] being captured with the system_user system function (domain/username).

Code Snippet

CREATE TRIGGER tr_RatesTable_U_UserOnly
ON RatesTable
FOR UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN

DECLARE @.User varchar(50)

IF EXISTS
( SELECT *
FROM inserted
WHERE User <> system_user
)
BEGIN
ROLLBACK
RAISERROR('Cannot UpDate This Record', 16, 1)
RETURN
END

GO

|||

Hi,

I am not familiar with the Wizard stuff, but I would expect that it does not cover holding the logic for that. But should have a look on the resulting queries the wizard produces, maybe you are able to tweak the Update statement to cover your logic. Anyway, using a trigger could be another option:

CREATE TRIGGER TRG_UPD_SomeTable
ON SomeTable
FOR UPDATE
AS
BEGIN

IF NOT EXISTS(SELECT * From INSERTED WHERE User = SUSER_NAME AND DateTime <= DATEADD(s,-5,GETDATE()))
RAISERROR('Update not allowed',16,1)

END

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de