October 17, 2006 at 9:11 am
Is it possible to create a trigger in SQL that retrieves and inserts information from a non-MSQL (pervasive) database?
Any ideas are greatly welcomed.
October 17, 2006 at 1:23 pm
Answer: in some manner it is probably possible.
Truth: It's is a very, very, very, very bad idea. It would take me forever to properly describe how bad an idea it is, but this comes close: It's worse than using a cursor.
October 17, 2006 at 2:30 pm
John, thanks for reply, but not all that useful.
When you are limited with the possibilities available, you need to think think of novel ways to achieve certain outcomes, this was just one way.
In a lowish volume environment for a medium size company, I don't think it is all that bad.
Cheers
October 18, 2006 at 8:10 am
The only way I could see you being able to do this is if you create a linked server to this Non-MSSQL database. As John stated before this is not a very good approach, but it should work as long as this other database platform is supported with linked servers.
regards,
Jason
Regards,
Jason P. Burnett
Senior DBA
October 18, 2006 at 8:29 am
Jason, thanks for the reply, much appreciated. At least I have a starting point.
Cheers
October 18, 2006 at 9:13 am
i'm assuming you want the trigger to insert data that is placed in an SQL table to also be propigated to a non-SQL table; you know that the virtual table[INSERTED] has all the records that the trigger is inserting into the table that the trigger affects.
CREATE TRIGGER tr1 ON Customers
AFTER INSERT AS
insert into somelinkedserver...accesstablename(vaue1,value2)
select value1,value2 from [INSERTED]
Now, while you can do it in a trigger if you have to, I'd suggest a scheduled job that calls a stored procedure to insert the data instead;
if the trigger fails because the remote database was not available, the data would not be inserted into my sample customers database either; the trigger would roll back.
doing it in an insert lets the job fail, and the subsequent call of the job would insert anything it missed the first time;
INSERT INTO somelinkedserver...accesstablename(vaue1,value2)
select value1,value2 from [INSERTED]
where value1 not in(SELECT VALUE1 FROM somelinkedserver...accesstablename)
Lowell
October 18, 2006 at 10:06 am
thanks, for the reply, appreciated.
i am not too concerned about the linked database being unavailable since this is where the transaction originates.
below explains the issue i face:
1. I have an ERP package which is creating Sales Orders to be picked in a warehouse management system.
2. The ERP package sits on a Pervasive database whilst the WMS is on SQL. Unfortunately the interface between the two systems leaves a little to be desired, in that alot of information in the ERP system never makes it to the WMS simply because the ERP system never passes it through to the WMS. The interface has been developed by the ERP vendor so their is little scope for changes.
3. I have three options: Rewrite the interface, Modify the WMS download or write a trigger(or something) that copies the missing information into the transitory table before it hits the WMS.
I guess I have to weigh up my options before comitting to anything.
October 18, 2006 at 11:05 am
From what you stated, I think I'd add a linked server for the Pervasive database, then write a procedure to grab the data from the ERP/Pervasive database, and insert that data into the WMS on sql server;
depending on how important the data is, I'd copy the data every half hour or so by running the scheduled procedure as a scheduled job; that way if the current interface sucks and doesn't copy all the data, the schedule job would pick up the slack. How does the current ERP--> WMS interface know what has been migrated/copied or not?
the only issue I see would be identifying which records in the Pervasive database to bopy, but I'm sure you know whatever the primary key is for that data.
Hope that helps a bit.
Lowell
October 18, 2006 at 11:09 am
that was my plan but on a much more frequeny basis
October 19, 2006 at 3:00 pm
There are creative and different ways of solving any problem. You did not state your business problem which limits anyone from giving you a proper solution. Despite the many ways of making a trigger jump through the hoops you desire, your solution is ripe for failure in many, many ways.
Telling you how to make a trigger do that is like handing a loaded gun to a... well, heck, you're never supposed to hand a loaded gun to anyone.
October 20, 2006 at 2:28 am
John, again your post is not all that useful. My question was specific because I wanted a specific answer. Jason and Lowell helped me out by pointing me in the right direction.
Unfortunately both your posts are nye on useless since you 1, don't answer the question asked and 2, don't explain why this is a bad idea. Lowell explain some of the pitfalls of this approach which was very welcome.
I went to this forum as I am a subscriber to the sqlservercentral newsletter and was wishing to obtain some information from some experts in the area. In other forums where I post regularly I try not to flame others when they are digging for information (even if it is a bad idea), but try to point them in the right direction, or if I have nothing constructive to say, I say nothing.
I hope you take this criticism on board.
Thanks
October 20, 2006 at 5:46 am
If you want an answer - take it.
Yes, you can.
Is it a good or bad idea - don't know.
Is it a good idea to buld a house using only axe?
_____________
Code for TallyGenerator
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply