Update Trigger?

Last Post 09 Oct 2007 02:34 AM by dstoltz. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
dstoltz
New Member
New Member

--
08 Oct 2007 05:31 AM
Hi All,

I have a USERS table I use for my site. I have integrated a forum with my site, and therefore have a USER_FORUM table that has the duplicated user info...so, 2 tables with some of the same info.

I would like to put an UPDATE trigger on the USER_FORUM table, so that if the user updates their email address, username, or password through the forum, the trigger will also update the user registration table with the same information.

My Questions:

1) Will this work? I think it will, just need some confirmation

2) I'm new to triggers, how do I do this ONLY for an UPDATE?

3) How do I make the trigger so that it knows if the email, username, or password was changed? I don't need the trigger to do anything if these fields don't change.

If anyone has some example code, I would greatly appreciate it!

Thanks!
dstoltz
New Member
New Member

--
08 Oct 2007 05:55 AM
#3 will be the hardest for me....I guess I don't need to do any verification if it has changed, just simply update the other table with current values for all three fields....?
dstoltz
New Member
New Member

--
09 Oct 2007 02:34 AM
Ok, well, perhaps some example code will help me...I've looked at books online, and have been all over google. Here's what I have so far:

create trigger forum_changes
on registration_users
for update as
if update(Username)
begin
update other_table
set username = inserted.Username
end


Is this even close? Should I be using "updated.Username" instead of "inserted"?

Also - how do I tell it to update ONLY that one row in the "other_table"....in other words, how do I say:
WHERE other_table.Username = old value before update?

I don't know - I'm completely confused.
dstoltz
New Member
New Member

--
10 Oct 2007 03:34 AM
Russel - many thanks for the code - that got me going....I have it working, with one question remaining:

Here's the code:
CREATE TRIGGER [forumchange] ON [dbo].[InstantASP_Users]
FOR UPDATE
AS
if update(username)
BEGIN
Declare @oldUser varchar(64)
Declare @newUser varchar(64)
Select @oldUser = username from deleted
Select @newUser = username from inserted
update test_table set username = @newUser where userName = @oldUser
END

My question is, (albeit a naive one), the WHERE clause will update ALL the rows in the table that share that username (there is no constraint on that column).

Is there a way to "grab" the ID of the row that the table is updating? so you could do something like:

update test_table set username = @newUser where userID = @ID

Do you know what I mean....? I would guess the trigger inherently "knows" what row is being updated - can I grab the primary key of that row to use in the WHERE clause?

Thanks!
dstoltz
New Member
New Member

--
10 Oct 2007 05:48 AM
If I understand you correctly:

There is a primary key, member_id in the table that is being updated.


Acceptable Use Policy
---