Triggers

---
--- Experiment with triggers. Create a table designed to hold phone numbers. Whenever a numbers is inserted
--- or updated populate a fields with the number in a standardized format.
---

use ReportBuilderTraining

---
--- create a utility scalar function that takes a string and strips the non-nummeric characters from it
---

-- cleanup from past runs
if object_id('dbo.fnStripNonNumbers') is not null
	drop FUNCTION [dbo].[fnStripNonNumbers]
go 

-- create function
CREATE FUNCTION [dbo].[fnStripNonNumbers]( @instr as nvarchar(4000) )
RETURNS nvarchar(4000)
AS
BEGIN


	declare @outstr nvarchar(4000) = ''
	declare @c int = 1

	while @c <= len(@instr)
		begin

			if substring(@instr, @c, 1) like '[0-9]'
				set @outstr = @outstr + substring(@instr, @c, 1)

			set @c = @c + 1

		end

	return @outstr

END

go

--- not, need to specify schema on functions
select dbo.fnStripNonNumbers('a1b2c')
go


---
--- create phone table that we'll put the trigger on
---

-- cleanup from past runs
if OBJECT_ID('dbo.Phone2') is not null
	drop table dbo.Phone2
go

create table Phone2 (
	 StudentID int not null
	,PhoneNumberType char(1) not null
	,Number nvarchar(50) not null
	,NumberFormatted nvarchar(50) null
	,constraint Phone2_PK primary key clustered (StudentId, PhoneNumberType)
)
go

insert into Phone2 (StudentId, PhoneNumberType, Number) values (1, 'H', '555.555.5555')
go

---
--- see how we will format numbers
---
select *, 
	case
		when len([dbo].[fnStripNonNumbers](Number)) = 10
			then '(' + left([dbo].[fnStripNonNumbers](Number), 3) + ') ' + substring([dbo].[fnStripNonNumbers](Number), 4, 3) + '-' + right([dbo].[fnStripNonNumbers](Number), 4)
		else Number
	end Number2
	from Phone2

go
---
--- Create the trigger on the table
---

CREATE TRIGGER dbo.FormatNumber
ON dbo.Phone2
AFTER INSERT, UPDATE
AS

	update dbo.Phone2
		set NumberFormatted = 
			case
				when len([dbo].[fnStripNonNumbers](b.Number)) = 10
					then '(' + left([dbo].[fnStripNonNumbers](b.Number), 3) + ') ' + substring([dbo].[fnStripNonNumbers](b.Number), 4, 3) + '-' + right([dbo].[fnStripNonNumbers](b.Number), 4)
				else b.Number
			end
		from dbo.Phone2 a
		join INSERTED b
		on a.StudentId=b.StudentId and a.PhoneNumberType=b.PhoneNumberType

GO


insert into Phone2 (StudentId, PhoneNumberType, Number) 
	values 
		 (2, 'H', '555.555.5555')
		,(3, 'H', '5555555555')
		,(4, 'H', '01-507-555-4576')
go

select *
	from dbo.Phone2

go


update dbo.Phone2 set Number=Number where StudentId=1

select * from dbo.Phone2