?
Solved

trigger syntax

Posted on 2006-03-28
4
Medium Priority
?
376 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
0
Comment
Question by:JakeyCakes
  • 3
4 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16312223
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
0
 

Author Comment

by:JakeyCakes
ID: 16312445
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
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 16312717
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
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16312759
D'OH, SORRY, forgot about "0" :-( :


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


0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question