Checking if a column has been updated

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 2005 Forums

Transact-SQL (2005)

Checking if a column has been updated

Author

Topic

henstock
Starting Member

23 Posts

Posted - 2007-07-30 : 05:58:39

Hi all, thanks in advance for taking the time to answer my question.

I'm new(ish) to SQL 2005 and have recently come up0 against a problem I'm trying to fix.

I'm sure there are many ways to solve this problem but I'm struggeling at the moment and I'd like to do it 'the best way'

I'm having to create a 'triggers' program. I have a table that has stored information on people would like inofrmation on

e.g. TableName, ChangeType, FieldName, Operator, Operand

so i can tell my MD wants a to have an email sent to him when an account is created with a TotalSalesValue of > 100,000

the email i've got working fine. The trouble I'm having is this.

when a table is updated or inserted i want to check the changes made in the table against those in the triggers table to see if any of the requirements match. If they do I want to send them an email.

I've looked into UPDATE() and Columns_Updated with Substring
I've tried looking into FOR's and If's and I've looked into sysObjects but everything I've looked at seems overly complex.

what is the best way to find columns that have been updated in a table and match those against a table?

Hope you can help
Thanks again

Dave H

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 06:02:36

A trigger is resided on a specific table.
Use UPDATED(col name here) and the INSERTED virtual table to che´ck your conditions.

Without having your table layout, there is not much more to help.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 06:03:50

Columns_Updated is the way to go here. however Columns_Updated is a bitmask in varbinary so you can't just
substring it.

look at the examples of Columns_Updated in BOL = Books Online = SQL server help

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

henstock
Starting Member

23 Posts

Posted - 2007-07-30 : 06:23:34

Hi Peso and Spirit

I looked into Updated first, problem is it only checks 1 field right? I'd need to check them all, and as in each table there are a variable amount of fields how would i go about checking each one, would it mean i'd have to have a missive statement checking each field in every table, also when a table gets modified I'd have to go in and add more statements

I did look into Colums_Updated too, but it only does 8 colums in a row, same kinda problem as with the Updated first, what happens if i do it say 5 times but there are only 3 fields in the whoel table?

what i'd really like to do is somehting like

SELECT 'fieldnames' FROM tblMyTable WHERE 'Fieldnames' <> INSERTED.'Fieldnames'

and have it return all the fields where there are differences, then i could run with it from that

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 06:34:34

For this, you have to resort to DYNAMIC SQL (http://www.sommarskog.se/dynamic_sql.html)

May I ask why you have to do this?

E 12°55'05.25"
N 56°04'39.16"

henstock
Starting Member

23 Posts

Posted - 2007-07-30 : 06:45:56

Hi Peso

I've created using asp.net an order tracking system, i've made a dynamic report engine that allows people to search the orders database by choosing a table and field to search on, so it creates it's own sql select to display the data, and I've also got some heavy duty reprots coming from the database

the third requirement the whole company wants is to have triggers that send emails to specific people that request it.
Currently I'm the only software engineer here so it wouldn't be too bad if i custom made triggers for people, but there are literally going to be hundreds of them, so i thoguth the ebst way was to create an application that that firstly stores what the people want to have a trigger set on, so they can add amend and delete that information, and secondly to actually create the trigger itself.

correct me if I'm wrong but i'm sure i read that each table can only have one update and one insert trigger atatched to it, so i can't create a new trigger on a specific table as a repsonse to some one entering in a record to teh tirggers table, so i thougth this was the only other way to go about it

I am open to suggestions though if you know a better way, I've been looking...

thanks :-)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 06:48:00

You can also have the "email thingies" in a stored procedure, scheduled to run every 10 minutes or so.

E 12°55'05.25"
N 56°04'39.16"

henstock
Starting Member

23 Posts

Posted - 2007-07-30 : 06:50:55

ahh i don't want to run the emails set on a time period, my MD would get annoyed if every 10 mins he got an email saying
"by the way this client has got a total sales value of > 100,000"

Kristen
Test

22859 Posts

Posted - 2007-07-30 : 06:51:32

Did I read this wrongly? I read that the requirement was to send the MD an Email for new accounts with a TotalSalesValue of > 100,000

For that you just need:


CREATE TRIGGER dbo.MyTrigger
ON dbo.MyTable
AFTER INSERT
AS
SET NOCOUNT ON

	SELECT	Col1, Col2, Col3
	FROM	inserted AS I
	WHERE	I.TotalSalesValue > 100000

GO

You then need to work out how to replace the "SELECT" with something that will send Emails. You can either use a CURSOR in the Trigger, or INSERT the values into a "work" table, and then have a scheduled task process that once in a while.

Points to note:

You need to build the trigger to be able to handle multiple rows. A trigger fires ONCE no matter how many rows are inserted - your data entry form probably only allows one new record at a time, but some future bulk-import might import thousands of rows at a time ...

Personally I would not have a Trigger send an Email. Sending Email is a relatively slow operation, and is reliant on external stuff like COM objects. All of which can conspire to ruin your day

, and cause blocking on the tables. And your boss may not be too thrilled to get 1,000 separate emails when you import a batch of juicy new customers!

I would insert the relevant new data into a "work" table, and have a job which ran either once a day or once an hour that sent an email if there were any new rows to process. That job can run as "slowly" as it needs to without mucking anything up. It can also aggregate ALL the new customers into a single Email - if that is a useful enhancement.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 06:55:25

quote:
Originally posted by henstock

ahh i don't want to run the emails set on a time period, my MD would get annoyed if every 10 mins he got an email saying
"by the way this client has got a total sales value of > 100,000"

And getting an email for EVERY record inserted is not annoying?

By checking for every ten minutes, you can send an email outside the scope of the transaction, with a complete list of all accounts viable.

E 12°55'05.25"
N 56°04'39.16"

henstock
Starting Member

23 Posts

Posted - 2007-07-30 : 07:00:14

Kristen, yes so in that example, the MD would choose to have a trigger...

on table 'SalesOrders'
where some one has 'inserted' a record
where the 'TotalSalesValue'
was '>'
than '£100,000'

so a record get entered into 'SalesOrders' when lets say an accountant enters them. after the insert a trigger fires, checks the triggers table and finds the above record matches and so send the email off to the MD, and if the finance director had one for the same but £50,000 it'd get sent to him as well

Kristen
Test

22859 Posts

Posted - 2007-07-30 : 07:02:15

"the whole company wants is to have triggers that send emails to specific people that request it"

I'd definitely store "changed data" in a Work Table then. And then have a job that processed that once in a while. That "job" can look at a "requirements" table and send appropriate emails.

For example:

In your work table add a BatchNo column.

First, get the next Batch Number:

DECLARE @NextBatchNo int
SELECT @NextBatchNo = COALESCE(MAX(BatchNo)+1), 1)

Then update all "unprocessed" rows to the new bathc number:

UPDATE U
SET BatchNo = @NextBatchNo
FROM MyTable AS U
WHERE BatchNo IS NULL

Then process all the rows in the Batch


DECLARE MyCur CURSOR FOR
SELECT W.Col1, W.Col2, ..., R.Col1, R.Col2, ...
FROM MyWorkTable AS W,
     MyEmailRulesTable
WHERE     W.BatchNo = @NextBatchNo
      AND R.IsActive = 1 -- Assuming that you have some sort of "flag" for relevant rules

OPEN MyCur

FETCH NEXT FROM MyCur
INTO @W_Col1, @W_Col2, ..., @R_Col1, @R_Col2, ...
WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @EmailContent = NULL

    IF @R_Col1 = 'BigNewCustomer' AND @W_TotalSalesValue > 100000
        THEN @EmailContent = @EmailContent + 'Big new customer'

    ... Test other Rules ...

   IF @EmailContent IS NOT NULL
   BEGIN
       ... Send Email to Recipent defined in the EmailRules table
   END

    FETCH NEXT FROM MyCur
    INTO @W_Col1, @W_Col2, ..., @R_Col1, @R_Col2, ...
END

CLOSE MyCur
DEALLOCATE MyCur

"i'm sure i read that each table can only have one update and one insert trigger attached to it"

You can have as many as you like, but its a maintenance, AND performance, nightmare.

Kristen

henstock
Starting Member

23 Posts

Posted - 2007-07-30 : 07:02:30

quote:
Originally posted by Peso

quote:
Originally posted by henstock

ahh i don't want to run the emails set on a time period, my MD would get annoyed if every 10 mins he got an email saying
"by the way this client has got a total sales value of > 100,000"
And getting an email for EVERY record inserted is not annoying?

By checking for every ten minutes, you can send an email outside the scope of the transaction, with a complete list of all accounts viable.

E 12°55'05.25"
N 56°04'39.16"

not every record only when the record updated meets a criteria

henstock
Starting Member

23 Posts

Posted - 2007-07-30 : 07:08:18

Umm, have i made a misatke somewhere while trying to exaplin what I'm trying to do? essentially I'm trying to dynamically create triggers on tables

I'm reading through that link you gave me Peso on dynamic SQL, it's very interesting, hopefully it'll give me a few ideas how to tackle this

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 07:16:39

note that mail sending is an async operation in sql 2005 and thus isn't problematic duration-wise.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

henstock
Starting Member

23 Posts

Posted - 2007-07-30 : 07:41:11

yeah I'm fine with that

i think I've setteled for a solution like this
the trigger usues dynamic SQL with a while loop and UPDATE

using that i can create a string that has all the field names that have different values from the ones on the INSERTED table
i can then send that that string and the table name to a stored procedure

will the INSERTED table still be viasble from a stored procedure? or is it only alive for the update trigger?

the stored procedure does a simple join against the passed in fields on the inserted table and the triggers table
giving me all records i need to do triggers for

then I'll send the email off to the right people (not figured that bit out yet...)

henstock
Starting Member

23 Posts

Posted - 2007-07-30 : 07:54:31

silly question, but how do you create a trigger using a trigger? it says you can't use a USE in a procedure

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 07:55:50

It is only alive in the trigger.
Whwn UPDATING a table, the INSERTED virtual table has the new values, and the DELETED virtual table has the old values.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 07:56:34

You use DYNAMIC SQL to create a trigger from a trigger.

I cannot advise this because this is really bad design!

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-07-30 : 08:05:21

"note that mail sending is an async operation in sql 2005 and thus isn't problematic duration-wise"

No way of knowing if the Email send failed then? I'd prefer to Catch that during the Email send, and then retry next time (for example). Otherwise if SMTP Service is stopped, or somesuch, the Email will be lost - which is probably a not-uncommon event during a reboot etc. as the services are shutdown in no particular order ...

"will the INSERTED table still be viasble from a stored procedure? or is it only alive for the update trigger?"

INSERTED table only has scope in the Trigger, however the Table itself will have ALREADY been updated, so if you pass the PK to the Sproc it can query the table directly for whatever columns it needs.

I still think doing this in a trigger is a bad idea, and you would be better to use a batch operation for it ...

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 08:23:21

> No way of knowing if the Email send failed then? I'd prefer to Catch that during the Email send, and then retry next time (for
> example). Otherwise if SMTP Service is stopped, or somesuch, the Email will be lost - which is probably a not-uncommon event
> during a reboot etc. as the services are shutdown in no particular order ...

Wrong

the mail system is built on top of service broker and as such you have access to sent, failed, etc... mail messages.
and while the mail message is in the queue it gets continouously re-sent.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources