INSTEAD OF Triggers
-
I am trying to build an INSERT and UPDATE Instead of Trigger for one table called Registration
I need to prevent insert or update of username that already exists in the registration table.
Any ideas on what I am doing wrong with these triggers??
Thanks
Jeff C
Code for INSTEAD OF INSERT Trigger
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE TRIGGER InsteadINSERTRegistrationTrigger on Registration
INSTEAD OF INSERT
AS
DECLARE @Username varchar
select @username = username from inserted
IF @username IS NOT NULL
BEGIN
IF EXISTS (SELECT Username from Registration where Registration.username = @username)
RAISERROR ('Username already exists',16,1)
ELSE
BEGIN
INSERT Registration (VRSId, Password, PasswordChangeRequired, ChallengeQuestionCode, ChallengeAnswer, PIN, PinChangeRequired,
RecordLocked, SourceOfLock, LockTs, LastChangeTs, LastChangeUser, SSN, UserName)
SELECT VRSId, Password, PasswordChangeRequired, ChallengeQuestionCode, ChallengeAnswer, PIN, PinChangeRequired,
RecordLocked, SourceOfLock, LockTs, LastChangeTs, LastChangeUser, SSN, UserName
FROM inserted
END
END
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Code for INSTEAD OF UPDATE Trigger
CREATE TRIGGER InsteadUPDATERegistrationTrigger on Registration
INSTEAD OF UPDATE
AS
DECLARE @Username varchar, @delusername varchar
select @username = username from inserted
select @Delusername = username from deleted
IF @username IS NOT NULL
BEGIN
IF EXISTS (SELECT Username from Registration where username = @username)
RAISERROR ('Username already exists',16,1)
ELSE
BEGIN
UPDATE Registration
SET Id = inserted.Id,
VRSId = inserted.VRSId,
Password = inserted.Password,
PasswordChangeRequired = inserted.PasswordChangeRequired,
ChallengeQuestionCode = inserted.ChallengeQuestionCode,
ChallengeAnswer = inserted.ChallengeAnswer,
PIN = inserted.PIN,
PinChangeRequired = inserted.PinChangeRequired,
RecordLocked = inserted.RecordLocked,
SourceOfLock = inserted.SourceOfLock,
LockTs = inserted.LockTs,
LastChangeTs = inserted.LastChangeTs,
LastChangeUser = inserted.LastChangeUser,
SSN = inserted.SSN,
UserName = inserted.Username
FROM inserted
where Registration.username = @delusername
END
END
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
jcollins -
jeff,
i'm not sure why you're using triggers for this - can't you just put a unique constraint on the username field?
that would be a lot simpler
MVDBA
-
On Update you will have a problem if the user wants to update soem other column. (without changing the username)
The best option is to use an Unique key on username. it will ensure that the field is unique. and all inserts and updates will fail if the user name exists.
If you still want to use Instead of trigger (use this oppertunity to learn) change the you may have to add additional line to Update trigger:
Declare @ID int
Select @ID = Id from Inserted
IF EXISTS (SELECT Username from Registration where username = @username and ID <>@ID)
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
-
i'd also suggest the following change
CREATE TRIGGER InsteadUPDATERegistrationTrigger on Registration
INSTEAD OF UPDATE
AS
if update(username)
begin
if (select inserted.username from inserted)(Select deleted.username from deleted)
begin
DECLARE @Username varchar, @delusername varchar
......
in this way you only perform the trigger if that column has been updated AND the update has changed it's value
MVDBA
-
Problem is:
select @username = username from inserted
You take only last username from inserted and ignore all previouse ones. And you cannot really predict which one will be the last in every particular case.
Use set oriented approach. And not only in triggers, but in triggers especially.
II EXISTS (SELECT Username from Registration R
INNER JOIN inserted i on R.username = i.username)
....
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply