June 21, 2005 at 12:41 pm
I am attempting to write a trigger which will fire whenever a record is inserted into a particular table on a db which is on a server outside of our firewall.
The trigger would simply copy the new record into a table in a db inside of our firewall then delete the original record that is outside of our firewall so that the record is not exposed for a any appreciable length of time.
I am new to triggers in SQL Server (have done many in Oracle) would it be best to call a DTS package from the trigger, write a stored procedure to do the copy and delete or code all of it in the trigger code?
Mildred
June 21, 2005 at 1:18 pm
Any reason why the record is not directly inserted in its final destination?
June 21, 2005 at 1:32 pm
Yes, we can not allow outside users to access our server that is inside the firewall. This app is actually allowing people to apply for jobs, so we don't want personal information on the outside server any longer than it has to be.
Mildred
June 21, 2005 at 1:52 pm
Maybe you can simply create a linked server. Then an instead of insert trigger would do this :
insert into LinkedServer.DbName.owner.Table (col1, col2, coln) Select col1, col2, coln from dbo.Inserted
June 22, 2005 at 12:31 pm
Again, a linked server is not an option due to security concerns with the internet server being outside of our firewall.
We would like to go ahead with a db trigger which would fire whenever a record is inserted into the employment application table on our internet server and copy the record to the server inside the firewall, then delete the record from the internet server. We have a process that runs now using a dts package but it is run on a scheduiled basis and we would prefer to have it event-driven (on insert into table) so that anyone applying for employment would not have thier personal data exposed for any amount of time.
I do not know if I can or how to call a dts package or a stored procedure from a trigger or if I will need to code a stored procedure or do the insert/delete within the trigger code.
Mildred
June 22, 2005 at 12:49 pm
hmm.
Make the app write the record to a text file on a shared drive on the server that only the 2 server logins an nothing else can access. Then have the 2nd server check that drive for a new file every X minutes and import the data. Won't as quick as the trigger though.
June 22, 2005 at 1:17 pm
Does that mean that it can not be done from within a trigger? Is there no way to call a DTS package from within a trigger?
Mildred
June 22, 2005 at 1:24 pm
If you don't want to link the servers it's impossible. You'll at least need a job on the outside server that can connect to the protected server, but you don't seem to want to allow that. If it's not possible, then you have to use some technic like I just explained... which will unevitably cause some delay.
Is there anyway that you could hard code the connection on the outside server in a job? It would make this easier and pretty unlikely to be hacked.
June 22, 2005 at 1:25 pm
Forgot to answer that one... It's a worst practice to call outside process from within a transaction (still in transaction in the trigger).
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply