JakeyCakes
asked on
trigger syntax
I am new to the trigger function in sql server, so can I have some help with the syntax in my trigger code below (I am trying to create a trigger that finds the first 3 letters in th field country and it has a number in it (like 'CF3'?) move the to the postcode field and set the country field as null)
CREATE TRIGGER [testtrigger] ON [dbo].[Business]
FOR UPDATE
AS
BEGIN
DECLARE @i int
DECLARE @c char(1)
SET @i=1
WHILE @i<=3
BEGIN
SET @c=SUBSTRING(b.Country,@i,
IF @c LIKE '[0-9]'
update Business set postcode = a.country
update Business set country = null
SET @i=@i+1
END
from Business a
join inserted b on (b.BusinessNo = b.BusinessNo)
END
CREATE TRIGGER [testtrigger] ON [dbo].[Business]
FOR UPDATE
AS
BEGIN
DECLARE @i int
DECLARE @c char(1)
SET @i=1
WHILE @i<=3
BEGIN
SET @c=SUBSTRING(b.Country,@i,
IF @c LIKE '[0-9]'
update Business set postcode = a.country
update Business set country = null
SET @i=@i+1
END
from Business a
join inserted b on (b.BusinessNo = b.BusinessNo)
END
ASKER
Scott , the trigger still isn't doing what I want e.g. 'CF2 5PH' in Country is not moved to postcode and the country for that record not set to null
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
D'OH, SORRY, forgot about "0" :-( :
WHERE PATINDEX('%[0-9]%', i.Country) BETWEEN 1 AND 3
WHERE PATINDEX('%[0-9]%', i.Country) BETWEEN 1 AND 3
ON [dbo].[Business]
FOR UPDATE
AS
IF @@ROWCOUNT = 0
RETURN
UPDATE Business
SET postCode = i.country,
country = NULL
FROM Business b
INNER JOIN inserted i ON i.businessNo = b.businessNo
WHERE PATINDEX('%[0-9]%', i.Country) <= 3