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 |
|
dpront
Starting Member
5 Posts |
 Posted - 2011-05-26 : 00:16:35
|
| Hi, I have managed to create a Trigger function for SQL Server inside a table. But what I want to do is to create a Trigger function on databaseA.Table that listens to databaseB.Table.Is that possible? This is what my SQL Script looks like: http://privatepaste.com/9d70756170But I got this error: Msg 8197, Level 16, State 4, Procedure TR1_P_PERSON_MAINTENANCE, Line 1The object 'dbo.TABLEB' does not exist or is invalid for this operation.I think I got that error because I don't have that table to begin with.Any comment/suggestion about this one?Thanks,Gerard |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-26 : 04:27:54
|
| A dml trigger has to be created on a table and be fired by actions on that table. You can create a trigger on databaseB table which affects databaseA table.You'll also get a lot more response if you don't try to get people to follow links. Post code that highlights the problem - and reduce it as much as possible.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
dpront
Starting Member
5 Posts |
Posted - 2011-05-26 : 04:37:06
|
| Hi, Thanks for the reply and the comment. I have just managed to do what you had suggested: Trigger function on databaseB which affects databaseA. But what I want is to have a Trigger function on databaseB which affects databaseB and listens to databaseA.If there's any document with regards to this scenario. It would be good also. |
|
|
|
dpront
Starting Member
5 Posts |
Posted - 2011-05-26 : 04:54:20
|
| Hi, Just for an update, I am now receiving this error:[b]Msg 2108, Level 15, State 1, Procedure TR1_P_PERSON_MAINTENANCE, Line 4[\b][b]Cannot create trigger on 'WVI_HR.dbo.PERSON_MAINTENANCE' as the target is not in the current database.[\b] |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-26 : 06:59:25
|
| A trigger is fired by a dml statement on a table.You can't have a trigger on a table fired by a dml statement on another statement - doesn't really make sense. Look at the create trigger stateement - there's no way of specifying two tables in the create clause - and why would you want to?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
dpront
Starting Member
5 Posts |
Posted - 2011-05-26 : 07:32:40
|
| So that I can be able to create a trigger function that listens on databaseA without modifying/touching databaseA. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-26 : 07:34:55
|
| Nope.You can create a job that polls though.Or you can use the profiler or an audit trail.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
dpront
Starting Member
5 Posts |
Posted - 2011-05-26 : 22:17:23
|
| Thanks for the help. |
|
|
|
|
|
|
|
|
SQL Server Trigger