December 13, 2002 at 6:23 am
I have a trigger that operates on an INSERT and an UPDATE. The trigger tidies up address and phone number field that have spaces, international dialling codes specified in the phone number, converts them to uppercase etc. etc.
This works great for a single INSERT or UPDATE. However, if I do a multiple update using the SQL Query Analyzer to update another field between a certain range of records, all of the fields I'm changing within the trigger sets all of the records to the last entry within the Inserted table !
Hence, If I do an update on 10 records to change a field to a particular value the address is now the same for all 10 records and I have just lost the 9 original addresses.
I noticed that doing a count on the Inserted table I have 10 records within this table.
How do I modify my trigger to stop this from happening ?
Thanks
Mike
December 13, 2002 at 6:48 am
Hi, Somthing like: -
update t
set t.c1 = ..
,t.c2 = ...
from
Table t inner join inserted i on t.pk = i.pk
Regards,
Andy Jones
.
December 13, 2002 at 7:08 am
Hi Andy,
I'm doing a similar thing to what you have listed except I'm doing my validation before the UPDATE, hence my variables will always hold the last values from the inserted table ! I need to some how validate and update ALL entries within the Inserted table.
e.g.
ALTER TRIGGER TR_InsertUpdate1 ON dbo.tblAddress
FOR INSERT
AS
BEGIN
DECLARE @education_check nvarchar(50), @education_flag int,
@address1_i nvarchar(50), @address2_i nvarchar(50), @address3_i nvarchar(50), @city_i nvarchar(50),
@telephone_i nvarchar(50), @facsimile_i nvarchar(50), @dialling_code nvarchar(10),
@web_address_i nvarchar(255),
@id_i int
/* READ INSERTED DATA - INSERT TRIGGER */
SELECT @education_check = I.company_name,
@address1_i = I.address1, @address2_i = I.address2, @address3_i = I.address3, @city_i = I.city,
@telephone_i = I.telephone, @facsimile_i = I.facsimile,
@web_address_i = I.web_address,
@id_i = I.address_id
FROM Inserted AS I
/* OBTAIN INTERNATIONAL DIALLING CODE */
SELECT @dialling_code = tblCountry.dialling_code
FROM tblAddress
INNER JOIN tblCountry ON tblCountry.country_code = tblAddress.country_code
WHERE tblAddress.address_id = @id_i
/* SHUFFLE THE ADDRESS TO REMOVE ANY BLANK LINES */
SET @address1_i = ISNULL(@address1_i, '')
SET @address2_i = ISNULL(@address2_i, '')
SET @address3_i = ISNULL(@address3_i, '')
SET @city_i = ISNULL(@city_i, '')
IF LEN(@address3_i) = 0
BEGIN
SET @address3_i = @city_i
SET @city_i = ''
END
IF LEN(@address2_i) = 0
BEGIN
SET @address2_i = @address3_i
SET @address3_i = @city_i
SET @city_i = ''
END
IF LEN(@address1_i) = 0
BEGIN
SET @address1_i = @address2_i
SET @address2_i = @address3_i
SET @address3_i = @city_i
SET @city_i = ''
END
/* REMOVE ANY COMMA'S AT THE END OF EACH ADDRESS LINE */
IF (RIGHT(@address1_i, 1) = ',')
BEGIN
SET @address1_i = LEFT(@address1_i, (LEN(@address1_i) - 1))
END
IF (RIGHT(@address2_i, 1) = ',')
BEGIN
SET @address2_i = LEFT(@address2_i, (LEN(@address2_i) - 1))
END
IF (RIGHT(@address3_i, 1) = ',')
BEGIN
SET @address3_i = LEFT(@address3_i, (LEN(@address3_i) - 1))
END
IF (RIGHT(@city_i, 1) = ',')
BEGIN
SET @city_i = LEFT(@city_i, (LEN(@city_i) - 1))
END
/* REMOVE ANY FULLSTOP'S AT THE END OF EACH ADDRESS LINE */
IF (RIGHT(@address1_i, 1) = '.')
BEGIN
SET @address1_i = LEFT(@address1_i, (LEN(@address1_i) - 1))
END
IF (RIGHT(@address2_i, 1) = '.')
BEGIN
SET @address2_i = LEFT(@address2_i, (LEN(@address2_i) - 1))
END
IF (RIGHT(@address3_i, 1) = '.')
BEGIN
SET @address3_i = LEFT(@address3_i, (LEN(@address3_i) - 1))
END
IF (RIGHT(@city_i, 1) = '.')
BEGIN
SET @city_i = LEFT(@city_i, (LEN(@city_i) - 1))
END
/* REMOVE HTTP:// FROM WEB ADDRESS */
SET @web_address_i = RTRIM(LTRIM(LOWER(ISNULL(@web_address_i, ''))))
IF (LEFT(@web_address_i, 7) = 'http://')
BEGIN
SET @web_address_i = RIGHT(@web_address_i, (LEN(@web_address_i) - 7))
END
/* SET THE EDUCATION FLAG TRUE IF THE COMPANY IS A EDUCTAION ESTABLISHMENT */
IF (CHARINDEX('UNIVERSITY', @education_check) > 0) or (CHARINDEX('UNIVERSITA', @education_check) > 0) or (CHARINDEX('UNIV', @education_check) > 0) or (CHARINDEX('COLLEGE', @education_check) > 0) or (CHARINDEX('SCHOOL', @education_check) > 0)
BEGIN
SET @education_flag = 1
END
ELSE
BEGIN
SET @education_flag = 0
END
/* STRIP INTERNATIONAL CODE FROM TELEPHONE AND FACSIMILE NUMBERS */
IF (LEFT(@telephone_i, LEN(@dialling_code)) = @dialling_code)
BEGIN
SET @telephone_i = RIGHT(@telephone_i, LEN(@telephone_i) - LEN(@dialling_code))
END
IF (LEFT(@facsimile_i, LEN(@dialling_code)) = @dialling_code)
BEGIN
SET @facsimile_i = RIGHT(@facsimile_i, LEN(@facsimile_i) - LEN(@dialling_code))
END
UPDATE tblAddress SET
account_number = RTRIM(LTRIM(UPPER(I.account_number))),
company_name = RTRIM(LTRIM(UPPER(I.company_name))),
address1 = RTRIM(LTRIM(UPPER(@address1_i))),
address2 = RTRIM(LTRIM(UPPER(@address2_i))),
address3 = RTRIM(LTRIM(UPPER(@address3_i))),
city = RTRIM(LTRIM(UPPER(@city_i))),
state = RTRIM(LTRIM(UPPER(I.state))),
post_code = RTRIM(LTRIM(UPPER(I.post_code))),
web_address = @web_address_i,
telephone = RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(@telephone_i, '-', ' '), ')', ''), '(', ''), '+', ''))),
facsimile = RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(@facsimile_i, '-', ' '), ')', ''), '(', ''), '+', ''))),
education = @education_flag
FROM tblAddress AS T INNER JOIN Inserted AS I
ON T.address_id = I.address_id
END
December 13, 2002 at 7:19 am
Hi, you will need to do this in a set based operation i.e. without the variables - as a start (un-tested): -
update t
set
t.address1 = case right(t.address1,1) when ',' then left( isnull(t.address1,''),len(t.address1) - 1) else isnull(t.address1) end
,t.c2 = ...
from
tblAddress t
inner join inserted i on t.address_id = i.address_id
inner join tblCountry c on t.country_code = c.country_code
Regards,
Andy Jones
.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply