October 20, 2009 at 11:51 am
I have a table that holds authentication information of users and a table that stores passwords.
My problem:
I need 2 triggers to do the following:
1) If password is updated in the auth table then INSERT new password into password table
ELSE no action
2) If the count in the password table = 9 then DELETE the 9th password in the password table
ELSE no action
I am not very good with triggers does anyone have any ideas?
October 20, 2009 at 11:57 am
CREATE TRIGGER [dbo].[trupdatePassword] ON [dbo].[tbUserInfo]
FOR UPDATE
AS
IF NOT UPDATE(passwd)
RETURN
INSERT INTO Yourtable
that is the basic syntax.
-Roy
October 20, 2009 at 12:03 pm
Is the Passwords table just a log of the most recent 9 (or less) passwords that someone has used?
If so, you could incorporate that into a single trigger that would first delete based on the row_number() function, then insert.
Something like:
create trigger PasswordLog on dbo.Users
after insert, update
as
set nocount on;
;with CTE (Row) as
(select
row_number()
over(partition by Passwords.UserID
order by Passwords.ChangedOn desc)
from dbo.Passwords
inner join inserted
on Passwords.UserID = inserted.UserID
inner join deleted
on inserted.UserID = deleted.UserID
and inserted.PWord != deleted.PWord)
delete from CTE
where Row >= 9;
insert into dbo.Passwords (UserID, PWord, ChangedOn)
select UserID, PWord, getdate()
from inserted
inner join deleted
on inserted.UserID = deleted.UserID
and inserted.PWord != deleted.PWord;
Generally speaking, you'd be better off placing code like that in the proc that's used to update the password in the main table. It's easier to maintain that way.
You'll have to modify the above to fit your tables. I had to guess what columns you'd have.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2009 at 12:05 pm
If you are doing the password update using stored procedures then I would put all that logic in the stored procedure. If you must do it in a trigger, Roy has pointed you in the right direction. You might also want to read the Introduction to DML Triggers[/url] article on this site.
What exactly do you mean by 9th password? Do you mean the oldest? Do you have a created date in the table?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 20, 2009 at 12:14 pm
I would like to delete the oldest password that is correct.
Chad
October 20, 2009 at 12:22 pm
If I were you, I would go with the advice gus and jack gave you. That is to have it in as part of the stored proc that updates the password. Does your password history table have a datetime field where you store info of when you changed the password? If so, you can use the code provided by GSquared.
-Roy
October 20, 2009 at 12:29 pm
The auth table looks like this
userid
, username
, password
, type_gateway
, dt_pubactive
, dt_pubexpire
, status
The password table looks like this:
auto number
, dt_create
, userid
, password
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply