Solved

TSQL - common trigger code in a function?

Posted on 2008-10-02
5
298 Views
Last Modified: 2012-05-05
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
Comment
Question by:PMH4514
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 125 total points
ID: 22624778
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
 

Author Comment

by:PMH4514
ID: 22624968
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
 

Author Comment

by:PMH4514
ID: 22625209
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
 

Author Comment

by:PMH4514
ID: 22625289
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
 

Author Comment

by:PMH4514
ID: 22633678
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question