Trigger Or Tracking Record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger Or Tracking Record

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-09-19 : 11:17:05

Hi guys,

I have two questions,

1) I am using trigger on table to use this syntax

Create Trigger_Name On Table_Name ...............
(Trigger can track if there is update/insert/del)
My question is, is there is any way i can create trigger on few columns insted of full table?

2) Insted of Trigger, because its very slow,, i can get information(which field are updated/new insert/ or del) from system table?

Please let me know if my question is not clear or need more information. Any reply would be big help for me. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 11:23:18

1, trigger is always created against a table and not for individual columns. so didn't understand the meaning of creating only on few columns? can you elaborate?
2,sorry not clear on this. please elaborate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mfemenel
Professor Frink

1421 Posts

Posted - 2011-09-19 : 11:27:40

The trigger will always fire as visakh stated however you can test within your trigger for which columns were updated and only take action if you care about them. Check out columns_updated in BOL for more information.

Mike
"oh, that monkey is going to pay"

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-09-19 : 11:33:20

Thanks for reply guys, ans sorry for my questions was not clear.

1) I Create trigger on table and table has 22 fields and i only want to trak 2 or 3 fields out of 22. That is why i am asking is there any way i can create a trigger on columns.

2) Second question was, is there any other way insted of trigger to track changes on my columns. For example any system table where i can grab which information are updated/new insert/deleted?

mfemenel
Professor Frink

1421 Posts

Posted - 2011-09-19 : 11:39:04

No, you can't create a trigger on a column directly. And no, to my knowledge there isn't a system table where you can find out which information changed. What I was getting at in my post is sort of an in between. You could create a trigger on the table and the first line would be an if statement using the columns_updated function to test to see if the columns which changed were the ones you cared about. If not, you do nothing and the trigger is pretty lightweight. If they were columns you cared about then you can track your changes by using the values in inserted/deleted.

Mike
"oh, that monkey is going to pay"

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-09-19 : 11:44:20

Once again thanks for reply,

I need your favour, Here is my Trigger first line syntax where and what syntax should i enter,

CREATE trigger Track_Geo on Customer_1 for insert, update, delete
as

declare @bit int ,

One more quick question, just for my knowledge, if i just want to trak Insert and update i can change my triger like this

CREATE trigger Track_Geo on Customer_1 for insert, update
as

declare @bit int ,

Is this syntax ok if i want to trak Insert & Update?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 11:57:37

the syntaxes are fine.

for checking only the actions on column 1 and column2 for example do like


CREATE trigger Track_Geo on Customer_1 for insert, update, delete
as
IF UPDATE(Column1) OR UPDATE(Column2)
....

please keep in mind this will trigger processing even if Column1 and Column2 were part of update without their values getting changed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-09-19 : 12:19:49

Visakh want to make sure syntax would be like this?

CREATE trigger Track_Geo on Customer_1 for insert, update, delete
as
IF UPDATE(Column1),UPDATE(Column2),UPDATE(Column3),
INSERT(Column1),INSERT(Column2),INSERT(Column3),
DELETE(Column1),DELETE(Column2),DELETE(Column3)
AS

IS this syntax look ok?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 12:47:17

quote:
Originally posted by Sonu619

Visakh want to make sure syntax would be like this?

CREATE trigger Track_Geo on Customer_1 for insert, update, delete
as
IF UPDATE(Column1),UPDATE(Column2),UPDATE(Column3),
INSERT(Column1),INSERT(Column2),INSERT(Column3),
DELETE(Column1),DELETE(Column2),DELETE(Column3)
AS

IS this syntax look ok?

nope.
i didnt get what you're trying to do here
please explain

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mfemenel
Professor Frink

1421 Posts

Posted - 2011-09-19 : 13:43:44

Here is a quick example of using update.
--Create a test table
create table dbo.test(col1 varchar(20), col2 varchar(20))
--Add some values
Insert into dbo.test(col1,col2) values('Test1','Test2')
--The words "Trigger Fired" will appear in your results window if col1 is updated.
Create trigger trgUpdate
on dbo.Test
for Update
as
if update(Col1)
Begin
select 'Trigger Fired'
END
--Trigger fired appears in result window
update dbo.test set col1='Test3'
--1 row affected appears in result window, trigger did not fire.
update dbo.test set col2='Test3'

Mike
"oh, that monkey is going to pay"

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-09-19 : 13:45:57

CREATE trigger Track_Geo on Customer_1 for insert, update, delete
as
IF UPDATE(Column1),UPDATE(Column2),UPDATE(Column3),
INSERT(Column1),INSERT(Column2),INSERT(Column3),
DELETE(Column1),DELETE(Column2),DELETE(Column3)
AS

Explanation:- I am trying to do through trigger,
If Column1 or Column2 and so on are UPDATE then Proceed
If Column1 or Column2 and so on are INSERT then Proceed
If Column1 or Column2 and so on are DELETE then Proceed

any idea?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 14:15:27

nope there are no functions called insert,delete etc. for handling that you have make use f magic tables INSERTED and DELETED.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources