Use trigger to modify column data type length

Question

My program will create a temp table which will drop after the program executed. The data type length is 8. But I want to change the length to 15 when I run the program using the trigger function in Sql Server. I have few table that need to change the length. Is there any way to change the length without stating the table name in trigger function?

Clarification: I have 100 programs which will create temporary table with different names. Each temp table will have user_id varchar(8). So i want to change the length to 15 . But i dont want to open my each program's source code to change it. is there a better way that you can suggest me?

You've picked the wrong tool for the job. This is not a job for triggers. — Damien_The_Unbeliever, Aug 11 '17 at 07:11
@Damien_The_Unbeliever can you suggest any other way can be done in sql server? Because i dont want to modify my program as I have more programs that need to be changed. — FullStack, Aug 11 '17 at 07:13
Any reason you can't simply alter the table with a one time script? — user6144226, Aug 11 '17 at 07:16
You question does not make sense. Please state clearly what you have and what you want to achieve. If you are not fluent enough in a specific technology / language to know how do it, it is better to simply state what you have and what you want. We will provide recommendations. Otherwise you are just confusing yourself and us. — Alex, Aug 11 '17 at 07:17
What is the type of your column? if it's string or binary, just use varchar(15)/varbinary(15) — sepupic, Aug 11 '17 at 07:28
I mean, this way when effective size of your data is 8, it will occupy only 8, and when 15, it will be 15 without you change anything — sepupic, Aug 11 '17 at 07:29
Please explain what you mean by Trigger function. What program do you run in trigger function? — Alex, Aug 11 '17 at 07:42
@Alex i have no idea about trigger. I just asked whether can use the trigger or not to perform the action. — FullStack, Aug 11 '17 at 07:43
@Alex lets say I have 100 programs which will create temporary table with different names. Each temp table will have user_id varchar(8). So i want to change the length to 15 . But i dont want to open my each program's source code to change it. is there a better way that you can suggest me? — FullStack, Aug 11 '17 at 07:46
https://stackoverflow.com/questions/9797739/sql-server-ddl-trigger-controlling-table-creation , I guess you could try doing this in a DDL trigger on table creation — user6144226, Aug 11 '17 at 08:00
Now I understand your problem. I don't think there is an easy way out of it. Going forward to prevent such problems you can use: [user defined data types](https://www.mssqltips.com/sqlservertip/1628/sql-server-user-defined-data-types-rules-and-defaults/) . This will allow you to simply change type definition, and your DB code will need no changes. — Alex, Aug 11 '17 at 08:01
So the question is not about altering table, but about altering YOUR CODE — sepupic, Aug 11 '17 at 08:51
@sepupic my question is about how to ALTER TABLE without modifying my code. — FullStack, Aug 13 '17 at 23:37

user6144226 · Answer 1 · 2017-08-11T08:38:45.043

What you want is essentially possible to achive using DDL triggers.

CREATE TRIGGER [TRG_TABLES]
ON DATABASE 
AFTER 
    CREATE_TABLE
AS 
BEGIN
    SET NOCOUNT ON

    DECLARE @TABLE_NAME SYSNAME

    SELECT 
        @TABLE_NAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME')

    IF EXISTS(SELECT * FROM   INFORMATION_SCHEMA.COLUMNS
          WHERE  TABLE_NAME = @TABLE_NAME
                 AND COLUMN_NAME = 'TEST') 
    BEGIN

        DECLARE @SQL as NVARCHAR(MAX) ='ALTER TABLE ' + @TABLE_NAME + ' ALTER COLUMN TEST NVARCHAR(200) '

        Exec sp_ExecuteSql @SQL
   END
END
GO

ENABLE TRIGGER [TRG_TABLES] ON DATABASE

You should be EXTRA careful about SQL injection if you use this approach.

EDIT: This is just a general idea you should probably figure out under which conditions you should alter the column - if there is a predictable pattern to your table names.

i have another concern. If I create a table without the 'TEST' column it throws error. What should i do to get rid of the error? — FullStack, Aug 11 '17 at 08:26
@FullStack https://stackoverflow.com/questions/133031/how-to-check-if-a-column-exists-in-sql-server-table — user6144226, Aug 11 '17 at 08:31

Use trigger to modify column data type length

1 Answers1