Solved

TSQL - common trigger code in a function?

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query 14 62
Update one rows based on previous row 5 36
Better way to filter date  - Query 5 45
SQL query 7 50
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…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

752 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