| Author |
Topic |
|
Zoma
Yak Posting Veteran
76 Posts |
 Posted - 2008-05-22 : 03:53:17
|
| I have problem with Triggers,Iv never done it before except @school!!One of our clients Server has same database names(WeighBridge) but Different Instances(1got Express and Other3 have SQL2005).There is a weighbridge scale on SQL Express Database.I want to create a Trigger that Automatically updates everytime there is weighbridge scale In other Databases that have SQL2005.If someone can help please a code or tell me what to do,Create a Trigger on a Table ot Database??Please Help!!!!!! |
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 2008-05-22 : 04:17:40
|
| Hi Zoma, Not sure what you are trying to achieve. The syntax for creating triggers is CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ ...n ] | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] } ] sql_statement [ ...n ] } } see http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/1/ if you need more examples. Cheers, |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-22 : 04:59:30
|
quote:
Originally posted by Zoma
I have problem with Triggers,Iv never done it before except @school!!One of our clients Server has same database names(WeighBridge) but Different Instances(1got Express and Other3 have SQL2005).There is a weighbridge scale on SQL Express Database.I want to create a Trigger that Automatically updates everytime there is weighbridge scale In other Databases that have SQL2005.If someone can help please a code or tell me what to do,Create a Trigger on a Table ot Database??Please Help!!!!!!
You mean for creation of table? |
|
|
|
Zoma
Yak Posting Veteran
76 Posts |
Posted - 2008-05-23 : 04:01:41
|
| I want too create a trigger that will automatically update to other databases when ever the wighbridge occurs!!Not only to one database. Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-23 : 04:39:32
|
quote:
Originally posted by Zoma
I want too create a trigger that will automatically update to other databases when ever the wighbridge occurs!!Not only to one database. Thanks
Whats wighbridge? is it name of database? |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2008-05-23 : 05:12:27
|
| Sounds to me like you need replication between the two instances, to keep the data in synch in your 2 different WeighBridge databases. I *believe* you can replicate to and from Express edition - I've not found anything saying you can't. Some questions you need to resolve, to help decide this:-do the changes need to be immediate? Transactional/Peer-Peer replaciation if yes, else merge/snapshot are also options-do changes happen on both databases - and if so, do both databases need to be udpated? if yes, then peer-peer or merge seem relevantThere are many other ways as well, linked-server (I wouldn't advice), Broker Services messages, SQL Server Notifiation Services (again, I wouldn't advise), and all kinds of other options (CLR, middle tier code, etc. etc.)*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2008-05-23 : 05:13:44
|
| If you only have changes in 1 database, and they need to go to mutliple other database, again, replication will handle this for you....*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-05-23 : 10:16:42
|
| The scope of a trigger should not extend beyond its database.Use replication to do this.Or use a trigger to populate a staging table within the database and have a scheduled job move data from the staging table to the recipient databases.e4 d5 xd5 Nf6 |
|
|
|
Zoma
Yak Posting Veteran
76 Posts |
Posted - 2008-05-30 : 02:19:45
|
| Thanks everyone. But i won't do replication. Because my manager dont want me to.Thanks again,Everything is sorted now,I craeted a package and i created a trigger to the database |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-05-30 : 10:36:41
|
| ...and it is going to break and crash your application.e4 d5 xd5 Nf6 |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-30 : 10:40:07
|
| Sure there will be big performance hits as Blindman suggested. |
|
|
|
Zoma
Yak Posting Veteran
76 Posts |
Posted - 2008-06-18 : 05:53:06
|
| Ya thanks Guys,I've finally convince them to do replication. Im just waitin for their approval.Thanks a lot again, Guess i have to wait until they say something!Peace |
|
|
|
|
Creating an Update Trigger