sql - INSTEAD OF INSERT trigger - clean value before insert-CodePudding

i want to make a trigger to clean phone number from NonNumeric characters before inserting into table.

Phone table looks like this:

rowId     PhoneNumber  DepartmentId   ...
 1        12345678      4             ...
 2        23456789      5             ...
 3        34255467      6             ...

i create this trigger:

CREATE TRIGGER tr_insertPhone ON [Phone]
instead of INSERT as
begin
    declare @Phone nvarchar(50)
    declare @DepartmentId int
    ...
    select @Phone = (select PhoneNumber from inserted)
    select @DepartmentId = (select DepartmentId from inserted)
    ...
    WHILE PATINDEX('%[^0-9]%',@Phone)>0
       set @Phone=STUFF(@Phone,PATINDEX('%[^0-9]%',@Phone),1,'')

    if (len(@Phone)>7) and (len(@Phone)<14) 
       INSERT INTO [Phone](PhoneNumber,DepartmentId,..) values (@Phone,@DepartmentId,..)
end

but while inserting i get the error:

Msg 512, Level 16, State 1, Procedure tr_insertPhone, Line 10 [Batch Start Line 1]
SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as as expression.

what am I doing wrong?

CodePudding user response：

It is not recommended to use the trigger to edit or format any fields. Maybe, you have often seen these commands inside the insert command. For example: insert into table1 (field1, field2) values (trim(@f1), @f2). That's why recommended to create a function for a do clean phone and use this in the insert command.

Example:

Create function clear_phone (@str varchar(200))  
returns varchar(200)  
begin  
    declare 
    @Phone varchar(200) 

    set @Phone = @str 

     WHILE PATINDEX('%[^0-9]%',@Phone)>0
       set @Phone = STUFF(@Phone,PATINDEX('%[^0-9]%',@Phone),1,'')

    return @Phone  
end

Example for using this function in the insert command:

insert into table 
(
    departmentId, 
    phoneNumber
) 
values (
    @depId, 
    clear_phone(@phoneNum)
)

CodePudding user response：

I'd strongly recommend you use a CHECK constraint for this, and do verification and cleaning in the application code

For example

ALTER TABLE Phone
  ADD CONSTRAINT ValidPhone
    CHECK (PhoneNumber NOT LIKE '%[^0-9]%');

If you really want to use an INSTEAD OF trigger to enforce this, make sure it can deal with multiple (or zero) rows.

Also, an inline tally table is much quicker than a WHILE loop, so we can break out each character, check it, and aggregate back up.

CREATE TRIGGER tr_insertPhone ON [Phone]
instead of INSERT as

INSERT INTO [Phone]
  (PhoneNumber, DepartmentId,..)
SELECT v.Phone, i.DepartmentId,..)
FROM inserted i
CROSS APPLY (
    SELECT Phone = STRING_AGG(SUBSTRING(i.Phone, v.indx, 1), '') WITHIN GROUP (ORDER BY v.indx
    FROM (VALUES   -- Recommended max phone length by ITU-T is 15
        (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)
    ) v(indx)
    WHERE v.indx <= LEN(i.Phone)
      AND SUBSTRING(i.Phone, v.indx, 1) LIKE '[0-9]'
) v
WHERE LEN(i.Phone) BETWEEN 7 AND 15;

GO