November 20, 2009 at 2:37 am
A trigger I created to check inserts on a table is actually preventing inserts happening on the table 🙁 What am I doing wrong?
Create Trigger username_email_fuzzy
On users
After Insert
As
Set nocount on
Declare @username varchar(max)
Declare @email varchar(max)
set @username = (select top 1 username from users order by created_date desc)
set @email = (select top 1 email from users order by created_date desc)
Insert into database_name.dbo.users (user_id,username,email,ip_address,credits,
total_added_credits,total_used_credits,last_login_date,created_date)
select user_id,
username,
email,
ip_address,
credits,
total_added_credits,
total_used_credits,
last_login_date,
created_date
from users
where (SOUNDEX(username) like SOUNDEX(@username)) or (SOUNDEX(email) like SOUNDEX(@email))
November 20, 2009 at 3:38 am
How do you know it's stopping inserts from happening?
It's an AFTER trigger, so unless you do a ROLLBACK or a DELETE (which I can't see), the original insert should be there.
The biggest problem I can see with the trigger is that it's referring to the "users" table, not the "inserted" table, so it's taking absolutely no notice of the row that's just been inserted.
What are you actually trying to achieve with this trigger?
November 20, 2009 at 4:19 am
Well, on the website front-end's users sign-up page, when the user clicks the submit button it returns an authorisation error message to the front-end that refers to the trigger and no row is added to the table. When I disable the trigger, users are able to sign-up again and rows are inserted to the table.
The objective for the trigger is to to check all new sign-up (a sort of fuzzy lookup on username and email) to find similar usernames and email address and write the results to another table in another database on the same server. I'm doing this because we have had allot of dodgy users signing up with usernames like "jesus1", jesus2","jesus123" to use free credits users they get on sign-up to manipulate games on the website, I wanted to flag these accounts up for daily review.
P.S What do you mean by this:
The biggest problem I can see with the trigger is that it's referring to the "users" table, not the "inserted" table, so it's taking absolutely no notice of the row that's just been inserted.
November 20, 2009 at 4:42 am
romanoplescia (11/20/2009)
P.S What do you mean by this:The biggest problem I can see with the trigger is that it's referring to the "users" table, not the "inserted" table, so it's taking absolutely no notice of the row that's just been inserted.
The purpose of a trigger is to give you access to the row(s) that have just been inserted/updated/deleted, so you can carry out some further activity, usually based on just those rows that have been affected by the triggering statement.
In your case, it is an INSERT trigger, so the row(s) just inserted are available to the trigger in the pseudo "inserted" table, which is what you should be referring to in your trigger.
Without table create scripts etc, I can only guess, but I would expect your trigger code to look something like this:
IF EXISTS(SELECT * FROM inserted i
INNER JOIN database_name.dbo.users u
ON SOUNDEX(u.username) like SOUNDEX(i.username)
OR SOUNDEX(u.email) like SOUNDEX(i.email))
ROLLBACK TRAN
That snippet is joining the inserted table (containing the rows you are trying to insert) to the existing users table using your soundex criteria, and rolling back the insert if it finds a match.
Do a bit of Googling on triggers for more examples.
November 26, 2009 at 4:37 pm
romanoplescia (11/20/2009)... I wanted to flag these accounts up for daily review...
Hi, can I suggest that you do not use a trigger as there is no immediate need.
You said yourself that they will be up for "daily review", so why not just create a report from the users table of matching accounts...?
You could run it for accounts created on any day you like whenever you like with no impact on users...
You could even run it overnight to avoid peak times...
Just a thought.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);November 27, 2009 at 2:58 am
mister.mango
I did eventually go down that route and just review new users flagged up by the report on a daily bases.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply