SQL cell wise trigger

Question

Can a specific cell wise trigger be created?

Or is

IF UPDATE(COLUMN) WHERE OTHER_COLUMN LIKE 'JT'

the equivalent present in SQL Server 2008?

EDIT after getting 2nd answer---

IF not UPDATE(CurrentNo) --// Wanted to do like this : where series ='JT'
    return

IF not EXISTS(SELECT 'True'
              FROM Inserted i
              JOIN Deleted d ON i.Series = d.Series
              WHERE i.Series = 'JT' AND d.Series = 'JT')
    return

Seems ok right! Please comment.

score 1 · Accepted Answer · answered Oct 11 '10 at 09:45

1

No. There is no way of doing this declaratively. You would need to create a general Update trigger and put logic in it to return immediately IF NOT UPDATE (column)

If the column of interest was updated then you would query the inserted and deleted pseudo tables to allow you to process rows where your condition of interest was met.

answered Oct 11 '10 at 09:45

Martin Smith

438,706
87
741
845

So, basically you are saying that it is possible. – Unreason Oct 11 '10 at 09:49
@Unreason - Kind of. It is not possible to get the trigger to fire only when a certain condition is met though. – Martin Smith Oct 11 '10 at 09:51
Hi, Can I create a #hashtemp table before update and use it after update? so that the pre-Update and post-Update cell values are matched and then trigger proceeds other wise returns or exits. If any one has done it already? – Rick2047 Oct 11 '10 at 09:58
@Rahul2047 You don't need to. That is what the `inserted` and `deleted` pseudo tables are for. You can join them on your PK column(s). The `inserted` table has the new values. The `deleted` table has the original values. – Martin Smith Oct 11 '10 at 10:00
@Martin Smith : I was not aware of these .. any link by where I could see the use or example of using these tables with their PK. I am getting "Invalid object name 'inserted'." error. I am not able to do sp_helptext for these. I tried google I could not find. Or I am not doing it correct way. thanks a lot Martin. – Rick2047 Oct 11 '10 at 10:24
1

@Rahul2047, you could read the docs now that you know it is possible (http://msdn.microsoft.com/en-us/library/ms189799.aspx). Work out the examples, it seems you are only having syntax problems. – Unreason Oct 11 '10 at 10:34
@Martin Smith, @Unreason: OK now I have implemented 'inserted and deleted pseudo' and on test server it works like anything. And this is so nice to optimize the trigger this way. Thanking you both. Although I was not having the syntax issues, but I noticed that the IDE of ms-sql server 2008 could not recognize some objects if those are session based or user based, like a hashtable.But I could not use the PK column. Is above edited option is ok? if not then I need to know about PK column. Thanks again. – Rick2047 Oct 12 '10 at 07:40

score 0 · Answer 2 · answered Oct 11 '10 at 09:53

Tiggers are specified on tables, not on rows, columns or cells. Inside the body of the trigger you will have access to the INSERTED and DELETED tables. You can join them together to deterimine which columns were changed during an update. The UPDATE() function which is available in SQL Server 2008 (as well as previous versions) is a shorthand method for determining whether a column has changed.

SQL cell wise trigger

2 Answers2

Linked