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.

Improve this question
3
  • Some element of anwser to your question here: stackoverflow.com/questions/1751715/… Commented May 30, 2012 at 7: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. Commented May 30, 2012 at 7: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) :(( Commented May 30, 2012 at 7:20

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Damien, could you explain me how it works a little bit? It works, but i don't know how...
@gaffcz - I've added some explanation, guessing you wanted info on inserted and deleted. If not, can you say which bit(s) are not clear to you?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.