We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

trigger syntax

JakeyCakes
JakeyCakes asked
on
Medium Priority
409 Views
Last Modified: 2012-08-13
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,1)
            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
Comment
Watch Question

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
CREATE TRIGGER [testtrigger]
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

Author

Commented:
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
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Hmm, odd, looks good to me.  Are you sure?  Or are there other triggers that might be "undoing" this?

Oh, also, you might want to change only those rows where the country has changed as part of the UPDATE:


CREATE TRIGGER [testtrigger]
ON [dbo].[Business]
FOR  UPDATE
AS
IF @@ROWCOUNT = 0
     RETURN
IF UPDATE(country)
BEGIN
    UPDATE Business
    SET postCode = i.country,
        country = NULL
    FROM Business b
    INNER JOIN inserted i ON i.businessNo = b.businessNo
    INNER JOIN deleted d ON i.businessNo = d.businessNo
    WHERE PATINDEX('%[0-9]%', i.Country) <= 3 AND i.Country <> d.Country
END --IF

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
D'OH, SORRY, forgot about "0" :-( :


WHERE PATINDEX('%[0-9]%', i.Country) BETWEEN 1 AND 3


Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.