Data Validation using a Stored Procedure or SQL Trigger

Question

Data Validation using a Stored Procedure or SQL Trigger

mbender

SSCrazy

Points: 2595
More actions
April 24, 2008 at 6:44 am

#185771

I'm new to stored procedures and sql triggers but what i'm looking to do is create a way that if someone trys to update a record in a table, that a trigger will either call a procedure or if I can do it in the trigger it self, checks to see if certain fields in the table are changing. If so it will insert it into a different table. I want to do this before the table updates. I'm new to this stuff but any help will be much appreciated.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

The code is the same either way. You need to use the UPDATED() function to check for changes, or you can compare values in the inserted and deleted virtual tables.

something like

create trigger ...

insert newtable

select i.colA

from inserted i

inner join deleted d

on i.pkcol = d.pkcol

where i.colA d.colA

You can lok up triggers in BOL to get an idea of how to work with the inserted and deleted tables.

mbender SSCrazy Points: 2595 More actions · Answer 2

BOL? Sorry pretty new to this stuff.

Thanks for your help

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

Sorry, Books Online is BOL. It's included with all SQL Server client installations, or you can view it here: http://www.sqlservercentral.com/Forums/Topic486887-263-1.aspx

here's the CREATE TRIGGER: http://msdn2.microsoft.com/en-us/library/ms189799.aspx

Apologies, but this almost sounds like a test question, so I'd like to see you do some work and attempt this. Happy to point you in the right direction and explain things better if you need it.

When a trigger fires, the update occurs on the table, but it isn't committed. The trigger is a part of the update/insert/delete transation. SQL Server creates two virtual tables, inserted and deleted, which contain the data coming in (inserted) and going out (deleted). For an update, you have both tables. you can join them with the regular table, with other tables, etc. to check for data.

The UPDATED function will help you determine if a column changed, but not it's value.

mbender SSCrazy Points: 2595 More actions · Answer 4

thank you will try that, appreciate all your help

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

Hi

How will the table be updated . Open the table and make the modifications , thru ad-hoc sql queries or thru stored procedures ?

"Keep Trying"