Trigger

[sardinka2]

I am trying to create a triger for insert. I would like to get id into log table id data was inserted.What am I doing incorrect:
if exists (select * from dbo.sysobjects where id = object_id('dbo.mstr_update')
and OBJECTPROPERTY(id, 'IsTrigger') = 1)
drop trigger dbo.mstr_update
GO
CREATE TRIGGER dbo.mstr_Log ON mstr_new
FOR INSERT
AS
Begin
SELECT ID,Getdate()
Into mstr_Log
FROM mstr_new

 

[SarahG]

You need to use the "inserted" table which is populated with your newly inserted record, so that you only retrieve the new record:

SELECT ID,Getdate()
Into mstr_Log
FROM inserted

Books Online covers this inserted table, and also the deleted table, both of which you will use in triggers.

Hope this helps

 

[sardinka2]

Thans it helps. Can I run a triger when spesific column was updated?

 

[SarahG]

Yes, you would need a trigger 'FOR UPDATE', and then you would join your table mstr_new with the inserted table and compare the columns, something like:

SELECT ID,Getdate()
Into mstr_Log
FROM inserted i INNER JOIN mstr_New m ON (m.PrimaryKey=i.PrimaryKey)
WHERE i.column<>m.column

The inserted table contains newly inserted records and the new values supplied by an update.

 

[sardinka2]

I mean exec trigger when spesific column updated (for example colum 2).
Thanks

 

[SarahG]

A trigger is activated when *rows* in a table are updated, deleted, inserted. You then have to explicitly check in the appropriate trigger to see if a column is changed, so by joining the inserted table with your own data table, you can check if the column has changed.