August 21, 2003 at 7:26 pm
Hi,
I may be barking up the wrong tree for this but
Can you fire a trigger (SP maybe?) that will stop a user from changing a record if it is more than a day old, I have the following in a trigger.
The idea is that a person can alter a transaction for a day but after that they cant touch the transaction, is a trigger the best way ?
CREATE TRIGGER [Muni_Stop_Updates] ON dbo.tblAppTrans
FOR UPDATE, DELETE
AS
Declare @changeday datetime
Declare @thisday datetime
SELECT @changeday = dtechngDateStamp, @thisday = dteDateStamp from tblapptrans
SET @dDay = CONVERT(INT, @thisday) - Convert(INT, @changeday)
IF ( DATEDIFF(day, @thisday, @changeday) <> 0 )
BEGIN
RAISERROR ('You have attempted to change a transaction. After a transaction is entered it cannot be changed.', 16, 1)
ROLLBACK TRANSACTION
END
------------------------------
Life is far too important to be taken seriously
August 21, 2003 at 10:26 pm
It seems a to be the correct way, since you need to validate at the row level.
The trigger will take care of the updations that are done from the frontend as well as those done directly from the backend.
Any one else can contribute some other technique?, cos this seems to be one of the key requirements of OLTP applications.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
August 21, 2003 at 10:46 pm
Maybe should clarify this: The trigger actually doesnt work!
it is supposed to compare the two date fields and if they are more than a day apart then the does nothing , otherwise throw up an error. the two are inserted with the same date via frontend. But if an update occurs then I want to trap the dtechangedate and compare it against the dtetimestamp date. Not sure if i have explained this properly. thanks brokenrulz for letting me know in in the right direction !
------------------------------
Life is far too important to be taken seriously
August 21, 2003 at 11:21 pm
Adam,
For both the UPDATE and DELETE scenario, whether it's a single row or multiple rows affected, your trigger can refer to the "deleted" table. An example follows.
(nb. For demonstration purposes I've changed your 1-day cutoff to a totally unrealistic 3-second limit)
create table tblAppTrans (
id int identity not null,
surname varchar(50) null,
dteDateStamp datetime null default current_timestamp
)
go
CREATE TRIGGER [Muni_Stop_Updates] ON dbo.tblAppTrans
FOR UPDATE, DELETE
AS
IF EXISTS (
SELECT * FROM deleted
WHERE DATEDIFF(second, dteDateStamp, GETDATE()) > 3)
BEGIN
RAISERROR ('You have attempted to change a transaction. After a transaction is entered it cannot be changed.', 16, 1)
ROLLBACK TRANSACTION
END
go
insert tblAppTrans (surname) values ('brown')
select * from tblAppTrans
go
update tblAppTrans set surname = 'white'
go
delete tblAppTrans
go
insert tblAppTrans (surname) values ('brown')
select * from tblAppTrans
go
waitfor delay '00:00:05'
update tblAppTrans set surname = 'white'
go
delete tblAppTrans
go
Cheers,
- Mark
Cheers,
- Mark
August 21, 2003 at 11:27 pm
quote:
SELECT @changeday = dtechngDateStamp, @thisday = dteDateStamp from tblapptrans
I think you are selecting all the records from the table there has to be a where clause that selects the updated row.
Do give your feedback
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
August 22, 2003 at 12:22 am
Hi adamcrv,
quote:
Can you fire a trigger (SP maybe?) that will stop a user from changing a record if it is more than a day old, I have the following in a trigger.The idea is that a person can alter a transaction for a day but after that they cant touch the transaction, is a trigger the best way ?
and you need to show new and old records together?
How do you determine, if a record is one day old? I mean, do you do something like GETDATE()-1, or is it enough when a records is entered the first day 11:50 PM and ten minuted later it is one day old?
What about having two queries. One that shows the editable rows, and a second one showing the 'read-only' rows?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 22, 2003 at 12:25 am
In addition to my first post.
What about verify the update status at the client with a simple sp.
If Not Updateable Then Exit Sub
...
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 22, 2003 at 1:01 am
Apart from us the crazy coders of the world building apps for undeserving clients. No one is at work at that time , except maybe us !
Thanks to Mark for the help with this one. Thanks to brokenrulz for pointing out the glaring mistake in my SQL :), I knew i had to have criteria there, just not sure what ! 🙂 I was unaware of the "deleted" and "inserted" tables but since becoming aware of them, whenever i search the books online for something, they show up...always the way!!!
Frank,
the point is valid and i have taken it under consideration. Usually the transactions get "doctored" a week or two weeks later. Therefore any updates done after a day should cover almost 99% of cases. It is mainly to tighten up business rules. Plus trying to prove the power of SQL server to the powers that be (aka bosses) that we need to leverage more power from SQL. Im about 2 months old when it comes to triggers and their abilities 🙂
Once again thanks to everyone who contributed. Ijust hope i can be as helpful as you guys when i get up to the number of posts you are at
Adam 🙂
------------------------------
Life is far too important to be taken seriously
August 22, 2003 at 1:08 am
quote:
Im about 2 months old when it comes to triggers and their abilities 🙂
May I introduce you to my seven week old baby
ROTFL.
I'll get another cup of coffee
Have a nice day
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply