Trigger Question

[wildbash]

Howdy,

Is it possible to create a trigger for a table, that checks the length of a string before it actually saves it to the table ? If the size of the string is greater that the size of the column length, I want it to save a truncated string.

So far I have the following, but I have no idea how to save a truncated string:

create trigger trtest
on test
for insert, update
as
declare @strsize smallint,
@colsize smallint

if update(testfield)
begin
select @colsize = col_length('test', 'testfield')
select @strsize = len(testfield) from TEST

if @strsize > @colsize
???
end;

Thanks in advance.

WB

 

[SQLSister]

Frankly I woudl do this at the user interface level rather than a trigger. I'm not sure you can do this at the trigger level because the trigger would not fire if the record doesn't meet the length of the field size. It's possible an instead of trigger might work since that fires before the record is saved, but still I suspect the field in the inserted table might be limited to the actual size of the field, I don't know for sure. I suppose you could try one and see.

As to how to truncate, pretty much every language including t-sql has some version of the Left key word. Just define the total number of charaters you want in it.

 

[vongrunt]

Inside trigger's scope there are two "virtual" tables:

inserted - holds new data for INSERT, UPDATE
deleted - holds old data for DELETE, UPDATE triggers

You can use these tables to compare old and new values.

To use triggers or not, that's another question