Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

TSQL - common trigger code in a function?

In this thread:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23771329.html

aaronakin kindly provided a generic trigger that would generate an insert statement for the given table.

Is it possible to abstract the code in the accepted answer of the referenced thread above, into a stored procedure so that the same code could be installed as a trigger on several tables? ie. I know I can't create a trigger for multiple tables, and I don't believe it's possible to pass a table as a variable (right?) so how might I abstract the body of that trigger into a stored procedure that I can call from several different triggers?

Thanks
0
PMH4514
Asked:
PMH4514
  • 4
1 Solution
 
momi_sabagCommented:
here you go

CREATE Procedure GetInsertStmts (@tableName sysname, @schemaName sysname)
AS
  DECLARE
      @SchemaID            INT,
      @TableID            INT,
      @Columns            VARCHAR(1000),
      @ColumnsTypes      VARCHAR(2000),
      @HasIdentity      BIT
 
  SELECT @SchemaID = schema_id, @TableID = parent_object_id
    FROM sys.objects
    WHERE object_id = object_id(quotename(@schemaName)+'.'+quotename(@tableName))
 
  SELECT @Columns = COALESCE(@Columns + ', ','') + '[' + [name] + ']'
       , @ColumnsTypes = COALESCE(@ColumnsTypes + ', ','') +
         CASE
           WHEN TYPE_NAME(system_type_id) IN ('tinyint','smallint','int','bigint'/*, etc*/)
             THEN '''+ CAST([' + [name] + '] AS VARCHAR(1000)) +'''
           WHEN TYPE_NAME(system_type_id) IN ('char','varchar','smalldatetime','datetime'/*, etc*/)
             THEN '''''''+ [' + [name] + '] +'''''''
           WHEN TYPE_NAME(system_type_id) IN ('nchar','nvarchar'/*, etc*/)
             THEN 'N''''''+ [' + [name] + '] +'''''''
         END
    FROM sys.columns
    WHERE object_id = @TableID
 
  IF EXISTS (SELECT * FROM sys.columns WHERE object_id = @TableID AND is_identity = 1)
    SET @HasIdentity = 1
  ELSE
    SET @HasIdentity = 0
 
  SELECT CASE @HasIdentity
           WHEN 1 THEN 'SET IDENTITY_INSERT '+ OBJECT_NAME(@TableID) +' ON; '
           ELSE ''
         END AS Query_IdentOn
       , 'INSERT INTO ['+ SCHEMA_NAME(@SchemaID) +'].['+ OBJECT_NAME(@TableID) +'] ('+ @Columns +') VALUES (' AS Query
       , CASE @HasIdentity
           WHEN 1 THEN 'SET IDENTITY_INSERT '+ OBJECT_NAME(@TableID) +' OFF;'
           ELSE ''
         END AS Query_IdentOff
       , *
    INTO ##i
    FROM #inserted
 
  DECLARE @SQL VARCHAR(MAX)
  SELECT @SQL = 'SELECT Query_IdentOn + Query + '''+ @ColumnsTypes +'); '' + Query_IdentOff FROM ##i'
  EXEC(@SQL)
 
  DROP TABLE ##i
GO
 


but before you call the procedure, inside your trigger you must perform
select *
into #inserted
from inserted
0
 
PMH4514Author Commented:
Great thanks! So how best do I call it in the trigger? Do I hard-code the table name and dbo into the call? What is the purpose of the select * into #inserted?
0
 
PMH4514Author Commented:
This does not seem to be working. It is running without errors, but @SQL ends up empty every time.
I notice that @TableID takes on a value of 0 in the initial select statement.
0
 
PMH4514Author Commented:
I changed it to accept the table and schema ID's as paramaters, and select them from the trigger. Then it works just fine, so thanks!
0
 
PMH4514Author Commented:
Any idea you might have as to why this only seems to work on one of my tables? I fire it off a series of times as data is inserted into several tables, and only one of them produces any output.. the return is NULL on all the rest..
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now