March 29, 2006 at 10:57 am
I have these two triggers and need them to work with a multi row update.
How would I go about doing this???
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
CREATE TRIGGER InsteadINSERTRegistrationTrigger on Registration
INSTEAD OF INSERT
AS
DECLARE @username varchar (100)
select @username = username from inserted
IF @username IS NOT NULL
BEGIN
IF EXISTS (SELECT Username from Registration where Registration.username = @username)
BEGIN
RAISERROR ('Username already exists',16,1)
RETURN
END
END
INSERT Registration (VRSId, Password, PasswordChangeRequired, ChallengeQuestionCode, ChallengeAnswer, PIN, PinChangeRequired,
RecordLocked, SourceOfLock, LockTs, LastChangeTs, LastChangeUser, SSN, UserName, RegistrationTs)
SELECT VRSId, Password, PasswordChangeRequired, ChallengeQuestionCode, ChallengeAnswer, PIN, PinChangeRequired,
RecordLocked, SourceOfLock, LockTs, LastChangeTs, LastChangeUser, SSN, UserName, RegistrationTs
FROM inserted
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE TRIGGER InsteadUPDATERegistrationTrigger on Registration
INSTEAD OF UPDATE
AS
DECLARE @Username varchar(100), @delusername varchar(100), @ID int
select @username = username from inserted
select @Delusername = username from deleted
select @ID = Id from Inserted
IF @username IS NOT NULL and Update(username)
BEGIN
IF EXISTS (SELECT Username from Registration where username = @username and ID <> @ID)
BEGIN
RAISERROR ('Username already exists',16,1)
RETURN
END
END
UPDATE Registration
SET
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,
RegistrationTs = inserted.RegistrationTs
FROM inserted
where Registration.Id = @Id
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
jcollins
March 29, 2006 at 11:00 am
A little "bug" in SQL 2000 for INSERTED/DELETED tables is that you must join it with the source table, otherwise you only return 1 row or an error depending the circumstance.
UPDATE t
SET y=z
FROM INSERTED i
INNER JOIN table1 t
on i.x = t.x
March 29, 2006 at 11:52 am
First you haven't said what is supposed to happen when some of the rows are accepted and some of the rows are not.
Second. From your code the raise error does not prevents the transaction from happening on the underlaying table (Registration). In other words there is no rollback in you code.
Third. It is easier to perform this checks with constrains than with triggers.
Cheers,
* Noel
March 30, 2006 at 6:37 pm
This trigger looks a bit like a conversion from Oracle with all the inherent pitfalls. Table Inserted includes all records stored by an after-statement trigger, not just a single row from a row-based trigger.
Remove the first part of the trigger where you reject duplicate usernames and replace it with a unique index/constraint on username in table Registration, assuming VRSId is your primary key. But first find all duplicate usernames. In Oracle, raise_application_error does a roll back, but not in SQL Svr just as noeld said.
Cheers,
Win
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply