Solved

TSQL - common trigger code in a function?

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

785 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