Solved

TSQL - common trigger code in a function?

Posted on 2008-10-02
5
285 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
  • 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

948 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now