creating a trigger for the default phone format using T-SQL

Question

I have created a trigger that ensures that the phone number is inserted in the default format into the database. It was created for the Oracle database. Now I'm working in SQL Server 2008 R2 and have troubles converting this trigger. Can anybody, please, help me to convert the following trigger into T-SQL?

create or replace trigger before_insert_update_locations  
BEFORE insert or update  
ON locations  
for each row  
BEGIN  
    if length(:new.phone) = 10 then  
       :new.phone := '('||substr(:new.phone,1,3)||') '||substr(:new.phone,4,3)||'-'||substr(:new.phone,7,4);  
    elsif length(:new.phone) = 7 then  
       :new.phone := '(907) '||substr(:new.phone,1,3)+'-'||substr(:new.phone,4,4);  
    end if;  
END;

score 2 · Answer 1 · answered Mar 11 '13 at 16:52

This should do it. Keep in mind that T-SQL triggers fire per statement. There is no "FOR EACH ROW" equivalent. So the trigger below fires after updates and inserts to adjust the values for the freshly updated/inserted rows. The "IF EXISTS" bit keeps it from doing anything if no phone numbers are updated.

CREATE TRIGGER UPDPH
ON locations
AFTER UPDATE, INSERT
AS
BEGIN
    IF EXISTS
        (
            SELECT NULL
            FROM
                inserted i
            LEFT JOIN
                locations l
                ON i.locationsID = l.locationsID
                AND 
                    (
                        i.phone <> l.phone
                        OR 
                        (   l.phone IS NULL
                            AND 
                            i.phone IS NOT NULL
                        )
                        OR
                        (   i.phone IS NULL
                            AND 
                            l.phone IS NOT NULL
                        )
                    )                   
        )
    UPDATE locations
    SET phone = 
        CASE 
            WHEN LEN(i.phone) = 10
                THEN '(' + left(i.phone, 3)
                    + ') '
                    + SUBSTRING(i.phone, 4, 3)
                    + '-'
                    + RIGHT(i.phone, 4)
            WHEN LEN(i.phone) = 7
                THEN '(907) '
                    + LEFT(i.phone, 3)
                    + '-'
                    + RIGHT(i.phone, 4)
            ELSE    
                i.phone
        END
    FROM
        inserted i
    JOIN
        locations 
        ON locations.locationsID = i.locationsID;
END

score 1 · Answer 2 · edited May 23 '17 at 11:47

Like @Jason Quinones said, Transact-SQL doesn't support FOR EACH ROW triggers. Neither does it support BEFORE ones, which is why both his and my answers offer an AFTER trigger. Though, as a matter of fact, there are INSTEAD OF triggers in T-SQL, which sometimes might be used as a replacement for BEFORE triggers, the two are nevertheless quite different (as the names may suggest).

Anyway, an AFTER trigger should work perfectly for this problem, so, here's another Transact-SQL version for you:

CREATE TRIGGER locations_format_phone
ON locations
AFTER INSERT, UPDATE
AS
BEGIN
  UPDATE locations
  SET phone = '(' + STUFF(STUFF(RIGHT('907' + RTRIM(i.phone), 10), 7, 0, '-'), 4, 0, ') ')
  FROM inserted AS i
  WHERE i.location_id = locations.location_id
    AND LEN(i.phone) IN (7, 10)
END

As you can see, the trigger performs an update only if the length of phone is either 7 or 10 characters. (Values of other lengths thus stay unmodified.)

If the value is one of the specified lengths, it is first made sure to be the length of 10: the default 907 prefix is added to it at the beginning and then the last 10 characters of the result are taken. So, just to illustrate how the method works, if the original length is 7, you get this:

'907' + '1234567'  ->  '9071234567'            ->  '9071234567'
                        ^^^^^^^^^^ (10 chars)      (same as before)

And if it is 10, this is what happens:

'907' + '1234567890'  ->  '9071234567890'            ->  '1234567890'
                              ^^^^^^^^^^ (10 chars)      (original value)

Next, the STUFF function is called twice to insert a - and a ) in the middle of the resulting number. Because the insert positions are related to the unchanged 10-digit number, the - is inserted first, then the ) (otherwise the shift of positions would need to be taken into account):

       #4  #7
        |  |
        v  v
0)  'xxxxxxxxxx'

1)  'xxxxxx-xxxx'

2)  'xxx) xxx-xxxx'

Finally, a ( is simply concatenated at the beginning.

One other thing worth keeping in mind in connection with this problem is that an AFTER UPDATE trigger updating the same table may cause itself to fire again, which is a phenomenon called trigger recursion. Trigger recursion is disabled by default, but if you are not sure if the corresponding option was never changed, you might want to verify its present state. Querying against sys.databases is one way:

SELECT is_recursive_triggers_on
FROM sys.databases
WHERE name = 'your database name'

As is_recursive_triggers_on is a bit column, 0 would stand for off and 1 for on.

The above trigger is designed in a way that does not prevent its unlimited recursion: even though nested calls would stop updating any rows at some point (because the LEN(i.phone) IN (7, 10) condition would become false eventually), the trigger would still keep being invoked. To fix that, you could simply add this check at the beginning:

IF NOT EXISTS (SELECT * FROM inserted)
  RETURN
;

Read more about recursive triggers here:

Create Nested Triggers

creating a trigger for the default phone format using T-SQL

2 Answers2