SQL Trigger to compare old and new value

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)

SQL Trigger to compare old and new value

Author

Topic

jbrady3324
Starting Member

5 Posts

Posted - 2011-10-20 : 15:36:23

This is my first time writing a SQL trigger.

Table
Refer
msgid(pk), userid(pk), folderid

The goal is for the trigger to execute if there is an update to folderid and to see if the old value of folderid is equal to the absolute value of the new folderid value. If so, then I want to insert all msgids, userids into ft_notify where refer.msgid=deleted.msgid and refer.msgid > 0

Here is what I have so far:

CREATE TRIGGER Update_Ft_Notify
ON refer
FOR UPDATE
AS
if update (folderid)
and <not sure how to code this part>
BEGIN
Insert Into FT_NOTIFY
Select msgid, userid, 1, ‘A’, null
From refer
Where refer.msgid = deleted.msgid and refer.folderid>0
END

Thanks! Looking forward to learning this all

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-20 : 16:28:38

and EXISTS (select 1 FROM inserted i inner join deleted d on i.msgid = d.msgid and d.folderid = ABS(i.folderid)

p.s. the select portion of your insert needs deleted adding to the from clause or you need to change the = in the where to an EXISTS.

If there is one row of those inserted that has that characteristic, but the others don't, do you want to insert all the changed rows or just the ones that qualify for that absolute value match?

--
Gail Shaw
SQL Server MVP

jbrady3324
Starting Member

5 Posts

Posted - 2011-10-20 : 16:45:54

quote:
Originally posted by GilaMonster

If there is one row of those inserted that has that characteristic, but the others don't, do you want to insert all the changed rows or just the ones that qualify for that absolute value match?

--

Hmm, not sure I follow. I want to insert all rows. For example the table looked like this:

Userid, msgid, folderid
2,5,8
1,5,9
3,5,8
2,6,10

User updates row 3 folderid to be -8

Userid, msgid, folderid
2,5,8
1,5,9
3,5,-8
2,6,10

I would want to insert rows 1 and 2, but not 3 and 4

Okay so now I have:

CREATE TRIGGER Update_Ft_Notify
ON refer
FOR UPDATE
AS
if update (folderid)
and EXISTS (select 1 FROM inserted i inner join deleted d on i.msgid = d.msgid and d.folderid = ABS(i.folderid)
BEGIN
Insert Into FT_NOTIFY
Select r.msgid, r.userid, 1, ‘A’, null
From refer r, deleted d
Where r.msgid = d.msgid and r.folderid>0
END

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-20 : 17:29:50

Why would you want to insert 1 and 2? I'd have thought 1 and 3. Row 2 had nothing to do with the update.

Currently the trigger will insert 1, 2 and 3 (though you're missing a bracket). What logic requires rows 1 and 2?

--
Gail Shaw
SQL Server MVP

jbrady3324
Starting Member

5 Posts

Posted - 2011-10-20 : 17:38:59

Without getting too complicated, ultimately is updating a search index and the goal is to update that particular message (msgid) in the search index with only userids who have a positive folderid for that msgid.

Why would the current log insert row 3 as well? That does not meet the criteria of refer.folderid > 0

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-20 : 18:45:30

You're right, it wouldn't. But, what about this...

Userid, msgid, folderid
2,5,8
1,5,9
3,5,8
2,6,10
3,6,5

now, in a single update, someone updates rows 3 and 5 to a folderid of -8. So, new data

Userid, msgid, folderid
2,5,8
1,5,9
3,5,-8
2,6,10
3,6,-8

Exists just checks to see if there is any row that's changed to it's negative value, and one has. So the insert occurs. The insert will insert rows 1 and 2 (because of the match on folder 5 to a row in deleted) and also row 4 (because folderid 6 is also in deleted)

If that's not what you want, you need the abs check in the where clause of the insert too.

--
Gail Shaw
SQL Server MVP

jbrady3324
Starting Member

5 Posts

Posted - 2011-10-20 : 18:49:28

I am not worried about that scenario. The way the code is written, it can only change the value from X to -X, not X to -Y. Thanks for the help!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-21 : 07:16:38

Don't ever assume that your app is the one and only way that changes can be made to a database. That's just plain asking for trouble.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources