Geek asks and answers

Create a temporary trigger data table

I am trying to create an audit trigger without having to specify a list of columns more than once.

For this purpose, I want to create a temporary table of INSERTED or DELETED data content in a trigger, and then process this in the audit table.

If I use this:

IF @ChangeType = 'D'
  SELECT * INTO #tmp FROM DELETED
ELSE
  SELECT * INTO #tmp FROM INSERTED

Then I get a compilation error in the second SELECT * INTO that the #tmp table already exists.

If I try to get around this with dynamic SQL:

SET @Sql = 'SELECT * INTO #tmp FROM '
IF @ChangeType = 'D'
   SET @Sql = @Sq + 'DELETED'
ELSE
   SET @Sql = @Sql + 'INSERTED'

EXEC (@Sql)

Then I get an error that the DELETED and INSERTED tables do not exist.

How can I get the INSERTED and DELETED tables in a trigger into a temporary or other table in memory?

+3
source share
3 answers

Try creating a temporary table outside if, for example:

SELECT TOP 0 * INTO #tmp FROM DELETED

IF @ChangeType = 'D'
  INSERT INTO #tmp SELECT * FROM DELETED
ELSE
  INSERT INTO #tmp SELECT * FROM INSERTED
+4

- temp table. SELECT-INTO SQL Server .

SELECT * INTO #tmp FROM DELETED WHERE 1=0
IF @ChangeType = 'D'
  INSERT #tmp SELECT * FROM DELETED
ELSE
  INSERT #tmp SELECT * FROM INSERTED
+1

, . , ...

(#temp) , (@temp), , . (, .)

SELECT INTO, .

, - ?

DECLARE @temp TABLE (id AS INT, val as INT)

IF @ChangeType = 'D'
   INSERT INTO @temp SELECT * FROM DELETED
ELSE
   INSERT INTO @temp SELECT * FROM INSERTED


Personally, I even avoid using * if possible. Your subsequent queries will only use certain fields, so I would only copy the fields that I used. This has an additional advantage: if fields are added to the table, the code is not interrupted ...

DECLARE @temp TABLE (id AS INT, val as INT)

IF @ChangeType = 'D'
   INSERT INTO @temp SELECT id, val FROM DELETED
ELSE
   INSERT INTO @temp SELECT id, val FROM INSERTED


In my opinion, the advantage of specifying fields (which you want to avoid) is that you can guarantee that you always copy only what you need.

0
source

More articles:


All Articles