1

I'm trying to create a trigger which is operating on table component and logging UPDATE, DELETE and INSERT operation to the table component_history

CREATE TRIGGER component_hist
ON component
AFTER INSERT, UPDATE, DELETE
AS

DECLARE
   @tr_action varchar(8),
   @tr_id_compoment int,
   @tr_name varchar(max),
   @tr_value int

IF COLUMNS_UPDATED() <> 0 -- delete or update?
BEGIN     
  SET @tr_action = 'UPDATE'
ELSE -- delete     
BEGIN
  SET @tr_action = 'DELETE'
END

INSERT INTO component_history VALUES (@tr_id_component, @tr_name, @tr_value, @tr_action);

How can I send information from columns (id, name, value) from table component into component_history?

I've tried:

SET 
@tr_id_component = component.id,
@tr_name = component.name,
@tr_value = component.value

but it reports:

Msg 4104, Level 16, State 1, Procedure component_hist, Line 22
The multi-part identifier "component.id" could not be bound.

gaffcz
  • 3,469
  • 14
  • 68
  • 108
  • Some element of anwser to your question here: http://stackoverflow.com/questions/1751715/sql-server-trigger-switching-insert-delete-update – David Brabant May 30 '12 at 07:11
  • 2
    Assuming at least one insert has already been successfully processed (so rows exist in the table), `EXISTS (SELECT * FROM component)` will always be true. And triggers fire once per statement, not once per row, so thinking of putting values into variables is always wrong-headed. – Damien_The_Unbeliever May 30 '12 at 07:16
  • It's a second thing, it was a construction for ORACLE, but MSSQL doesn't support BEFORE statement, so I'm using AFTER only for now (I don't know how to solve BEFORE) :(( – gaffcz May 30 '12 at 07:20

1 Answers1

3

Something like this should suffice:

CREATE TRIGGER component_hist
ON component
AFTER INSERT, UPDATE, DELETE
AS

INSERT INTO component_history /* I'd prefer to see a column list here */
select id,name,value,CASE WHEN EXISTS(select * from deleted) THEN 'UPDATE' ELSE 'INSERT' END
from inserted
UNION ALL
select id,name,value,'DELETE'
from deleted
where not exists(select * from inserted)

A small optimization (if you have lots of large updates) would be to move the EXISTS (select * from deleted) evaluation out of the SELECT (since it will currently be assessed once per row).

Note, also, that in the UPDATE case, we're just storing the new values, not the old values.

The inserted and deleted pseudo-tables are special - they contain the rows that were affected by the statement that caused the trigger to fire. The inserted table contains any new rows, and the deleted table contains any old rows. This leads to the logic that during an insert, deleted will be empty (no old rows were affected by the insert). And during a delete, inserted will be empty (no new rows have been created). During an update, both tables are populated.

This is why the top part of the select applies to both insert and update operations, because we're using the inserted table. But we check whether there are any rows in deleted, to distinguish the two operations. In the bottom select (below the union all), we query the deleted table - but we use the where clause to prevent any rows being returned if this is actually an update operation.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448