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 |
|
vbjohn
Starting Member
32 Posts |
 Posted - 2011-02-22 : 11:06:45
|
| I need to create a trigger that is supposed to check if that line that someone is editing is in production or not. If it is in production then the update/insert/delete will not work. But if it is not in production then go ahead with the update/insert/delete. Can someone point me in the correct direction? |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2011-02-22 : 11:20:24
|
| You can make use of linked server and link the production server.In the trigger check using four part name whether the record exists or not and thereafter allow the user to continue the operation. |
 |
|
|
vbjohn
Starting Member
32 Posts |
Posted - 2011-02-22 : 11:21:00
|
| Basically before the update happens. See if that record that they are trying to modify is in production, and if it is then do not update. But, if it is not in production then go ahead and update.Record is stored in the database and one of the fields associated with it is: list_cde if that is 0 then update if it is greater than 0 then do not update. |
|
|
|
vbjohn
Starting Member
32 Posts |
Posted - 2011-02-24 : 12:34:21
|
| No sure I follow you pk_bohra |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-24 : 12:47:01
|
If your production database is placed on the same machine as that of your database where you need to perform the DML operation you can lookup the existance of the record via Four Part name if not exists (Select * from ServerName.DatabaseName.Dbo.TableName where ColName=value) then perform the DML else ReturnIf its not on the same machine then define/add the production server as Linked Server onto the machine/server where you want to perform the DML operation. Google about Linked Servers in SQL server and you will get the idea what it is and how it works  CheersMIK |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-02-25 : 15:47:28
|
| It sounds to me like vbjohn's production data is in the same server, same database, and same table as non-production data. I wonder if vbjohn's phrase "in production" is throwing us off. Perhaps he is not referring to an environment but rather it is a term used his business. Just an attribute of the row.In that case a trigger only allowing the action if the [list_cde] = 0 is possible. Probably an Instead Of trigger would be easiest. One question though: When the [list_cde] > 0 do you need to inform the user that their I/U/D didn't occur?Please look at CREATE TRIGGER in Books Online. Give it a try yourself and if you need help then post what you've attempted as well as the DDL of your subject table. Be One with the OptimizerTG |
|
|
|
|
|
|
Trigger - Update/Insert/Delete