create procedure sys.sp_MSscript_trigger_update_checks
(
@objid int,
@identity_col sysname,
@ts_col sysname,
@op_type varchar(3) = 'ins', -- 'ins', 'upd'
@indent int = 0
)
as
begin
set nocount on
declare @cmd nvarchar(4000)
,@colname sysname
,@ccoltype sysname
,@this_col int
,@rc int
-- script column checks for Image/Text
declare #hccols cursor local fast_forward for
select column_id
from sys.columns
where object_id = @objid
and is_computed = 0
order by column_id asc
open #hccols
fetch #hccols into @this_col
while (@@fetch_status != -1)
begin
-- get the column information
exec @rc = sys.sp_MSget_colinfo @objid, @this_col, null, 1, @colname output, @ccoltype output
if @rc = 0
begin
if @ccoltype in ('text','ntext','image')
begin
-- Image/Text data
if @op_type = 'ins'
begin
select @cmd = N'
if update(' + quotename(@colname) + ')
exec sys.sp_MSreplraiserror 20508 '
insert into #proctext(procedure_text) values(@cmd)
end
else if @op_type = 'upd'
begin
select @cmd = N'
if update(' + quotename(@colname) + ')
begin
exec sys.sp_MSreplraiserror 20509
goto FAILURE
end '
insert into #proctext(procedure_text) values(@cmd)
end
end
end -- if rc = 0
-- get next column
fetch #hccols into @this_col
end -- fetch_status
close #hccols
deallocate #hccols
-- script identity col update check
if @op_type = 'upd' and @identity_col is not null
begin
select @cmd = N'
if update(' + quotename(@identity_col) + N')
begin
exec sys.sp_MSreplraiserror 20510
goto FAILURE
end '
insert into #proctext(procedure_text) values(@cmd)
end
-- scipt timestamp col update check
if @op_type = 'upd' and @ts_col is not null
begin
select @cmd = N'
if update(' + quotename(@ts_col) + N')
begin
exec sys.sp_MSreplraiserror 20511
goto FAILURE
end '
insert into #proctext(procedure_text) values(@cmd)
end
-- all done
return 0
end