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.
| Author |
Topic |
|
PGG123
Yak Posting Veteran
55 Posts |
 Posted - 2004-08-16 : 12:13:41
|
| a particular field is updated in another table.This is my fist dabble on triggers and need all the help I can get.USE DB1DROP TRIGGER tr1GOCREATE TRIGGER tr1 ON dbo.CompNamesFOR UPDATE AS -- Here I need to check if a field in CompNames is updated. The field (called blnLocked) has a data type bit and either 0 or 1 should trigger the update. UPDATE dbo.Transactions SET blnLocked = -- This field must be updated with whatever value CompNames.blnLocked was set to. If it was set to 0, then Transactions.blnLocked must also be set to 0. WHERE ICN = @company AND dteCycleDate = @cycle -- These variables I should get from the updated CompNames record.GOThanks for your help. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-16 : 12:45:20
|
| You just need to use the inserted and deleted tables. This gets updated with the new record. The deleted table holds the old information.If the blnLocked value in inserted <> deleted then update.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
PGG123
Yak Posting Veteran
55 Posts |
Posted - 2004-08-16 : 13:29:01
|
| Thanks derrickleggett.Q: What fields are found in the inserted and deleted record? Just the blnLocked field in CompNames? How do I code to check for the blnLocked value in these two tables? If I use inner join, what field do I use to join them?What happens to these temporary resident tables? When do they get deleted? Or do they? If not, then new records are appended to to these tables after evry trigger action? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-16 : 13:31:32
|
| The inserted and deleted tables are copies of whatever table you inserted, updated, or deleted data from. Do you have an identity column on this table? These tables are specific to the context of the action. They do not exist outside of it, so there are no appends. Each time you execute a statement, the tables will be populated. The triggers will operate off that particular execution.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
trigger to update a field in a table when ...