Sql Server Programming :: Using trigger between two different databases


	Using trigger between two different databases "You can create a trigger only in the current database, although !!!!!!! a trigger can reference objects outside of the current database." I found thit inifo in SQL 2000 books online now i want to understand is it applicable to use trigger between two different databases .... may be on different servers or not ? if yes then how ? (can i use the regular syntax) like: USE database1 GO CREATE TRIGGER TEST ON database1.table1 FOR INSERT as insert into database2.table2(column in table 2 ) values ('any data value') GO if no ? then how to send any inserted or updated data from table in database1 to another table in database2 ........... may be on different servers (can i use DTS in SQL 2000 or SSIS in SQL 2005) if any one catch my point i will be eager to hear from him thanks ------------------------------------------- Best Regards, Mahmoud A.Shaban Of course you can reference remote objects in your triggers. Use three or four-part names for remote objects (depending on whether they're on the same server or not). The easiest thing for you to do would be to simply try. :) You cannot, however, create or alter triggers on remote objects. E.g.: this is not allowed: use db1 create trigger dbo.trigger on db2.dbo.table ... ML --- http://milambda.blogspot.com/ -----------------------------------------------Reply----------------------------------------------- thanks for your time your answer is to try but the syntax which i'm using is not allowed ?? -- Best Regards, Mahmoud A.Shaban - Hide quoted text - "ML" wrote: > Of course you can reference remote objects in your triggers. Use three or > four-part names for remote objects (depending on whether they're on the same > server or not). > The easiest thing for you to do would be to simply try. :) > You cannot, however, create or alter triggers on remote objects. > E.g.: this is not allowed: > use db1 > create trigger dbo.trigger > on db2.dbo.table > ... > ML > --- > http://milambda.blogspot.com/ On May 29, 12:39 pm, Mahmoud Shaban - Hide quoted text - <MahmoudSha@discussions.microsoft.com> wrote: > "You can create a trigger only in the current database, although !!!!!!! > a trigger can reference objects outside of the current database." > I found thit inifo in SQL 2000 books online > now i want to understand is it applicable to use trigger between two > different databases .... may be on different servers or not ? > if yes then how ? (can i use the regular syntax) > like: > USE database1 > GO > CREATE TRIGGER TEST > ON database1.table1 > FOR INSERT as > insert into database2.table2(column in table 2 ) > values ('any data value') > GO > if no ? then how to send any inserted or updated data from table in > database1 to another table in database2 ........... may be on different > servers > (can i use DTS in SQL 2000 or SSIS in SQL 2005) > if any one catch my point i will be eager to hear from him > thanks > ------------------------------------------- > Best Regards, > Mahmoud A.Shaban I hope you realize that such triggers give you only limited protection. For instance, you can restore one of your databases from an earlier backup. This action does not fire triggers and it can violate your business rules. -----------------------------------------------Reply----------------------------------------------- You need to include the owner (2000) or schema (2005) between databasename and tablename. For example, assuming dbo: insert into database2.dbo.table2(column in table 2 )... RLF "Mahmoud Shaban" <MahmoudSha@discussions.microsoft.com> wrote in message news:A412ADEC-2A36-460A-899F-0C7B4724F468@microsoft.com... - Hide quoted text - > "You can create a trigger only in the current database, although !!!!!!! > a trigger can reference objects outside of the current database." > I found thit inifo in SQL 2000 books online > now i want to understand is it applicable to use trigger between two > different databases .... may be on different servers or not ? > if yes then how ? (can i use the regular syntax) > like: > USE database1 > GO > CREATE TRIGGER TEST > ON database1.table1 > FOR INSERT as > insert into database2.table2(column in table 2 ) > values ('any data value') > GO > if no ? then how to send any inserted or updated data from table in > database1 to another table in database2 ........... may be on different > servers > (can i use DTS in SQL 2000 or SSIS in SQL 2005) > if any one catch my point i will be eager to hear from him > thanks > ------------------------------------------- > Best Regards, > Mahmoud A.Shaban When you CREATE the trigger, you must be in the context of the database where the parent object resides. So instead of: >> use db1 >> create trigger dbo.trigger >> on db2.dbo.table Say: use db2; create trigger dbo.trigger on dbo.table ... -- Aaron Bertrand SQL Server MVP http://www.sqlblog.com/ http://www.aspfaq.com/5006