Trigger a procedure to execute when a table value changes

[murty]

Hello,

I have a Process Control System (PLC and DCS) which transfers values via OPC to a ODBC SQL 2005 database. The Control System side of things and the OPC to ODBS connections I have no problems with but due to my lack of SQL 2005 knowledge I cannot carry out the database side.

What I want to do is send a single bit (1 or 0) from the control system to the SQL 2005 database. On receiving this bit the database executes a procedure and then sets another bit which is sent back to the control system to say the email was sent.

I have setup a table 'DCSemail' with two bits: 'SendEmail' (Command to send email) and 'EmailSent' (Says email has been sent).

I also have a procedure setup, when executed, sends an email to my email address.

How do I trigger this procedure that will send my emails when the SendEmail becomes a 1? How do I reset this bit and set the EmailSent bit? I only want to use a table that has these two bits, the one I have created seems to record the 'True' or 'False' value every few seconds and creates a long list of records.

Any help would be grately appreciated. Please remember, I am a complete novice when it comes to SQL 2005 so be gentle!

Thanks,
Mark

[skhanal]

How are you sending bit from PCS system?. Is it writing to a table?

If PCS is writing to a table, you can write a trigger on DCSemail table which fires on UPDATE of a row and send email.

You can reset the bit within the trigger as well.

What is the data type of the column? If you are using bit then it can take 0 or 1, however if you edit it from SQL Server Managment Studio, it will appear as True or False. That is because of .Net translating the value. If you use INSERT statement, you can insert 0 or 1, SELECT shows 0 or 1 as well.