TRIGGER with a VIEW from a Linked Servers

[yboujraf]

Hi,

I have an ACCESS DB (.mdb) generate by an Automation.
Now, I would like to:

1/ Copy all datas from the ACCESS DBin my SQL Database.
I using DTS. All are working.

But, after copying, I would like at each time a new record is inserted in the ACCESS DB, I copy the this Record in my SQL DATABASE (synchronize the SQL DATABASE).
For this, I create LINKED SERVERS for the ACCESS DB (Named LOG).
In my SQL DATABASE, I create a VIEW that read one TABLE from LOG (linked ACCESS DB).
Now, I would like to create a TRIGGER from this VIEW for retrieve each INSERT and copy this insert in my SQL DATABASE.

Please find the CODE below :



CREATE TRIGGER trgLog on [dbo].[VIEW_LOG]
INSTEAD OF INSERT
AS

BEGIN
declare
@Log_Date datetime,
@Log_Input smallint,
@Log_Level smallint,
@Log_Milliseconds smallint,
@Log_Output smallint,
@Log_Panel smallint,
@Log_Response smallint,
@Log_Status smallint

set @Log_Date ='05/07/2004 00:00:00'
set @Log_Input =1
set @Log_Level =1
set @Log_Milliseconds =1
set @Log_Output =1
set @Log_Panel =1
set @Log_Response =1
set @Log_Status =1

exec InsertLog @Log_Date,@Log_Input,
@Log_Level,@Log_Milliseconds,
@Log_Output,@Log_Panel,
@Log_Response,@Log_Status

END

So in this code I Insert a test message.
But when I refresh my SQL DATABASE, I see nothing whereas I the ACCESS DATABASE the AUTOMATION insert record.

Can You Help me about that ?

Best Regards

 

[nigelrivett]

Triggers act on sql server tables not on access tables.

Think about what you are trying to do. Get sql server to prevent an insert in an access database and execute code in a sql server database.

Had a look in bol for any hint of this and could only find
>> A trigger is created only in the current database; however, a trigger can reference objects outside the current database.

But anyway a trigger is associated with the tr log and as an update in an access dtabase doesn't get recorded in a sql server log it can't fire a trigger.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

And of course a trigger o a view only fires when the update is made to the view not to the underlying table.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[yboujraf]

Please find this link and after says me why it is working :

http://www.experts-exchange.com/Databases/Oracle/Tools_Development/Q_20696158.html

 

[nigelrivett]

That's a trigger on an oracle table which is being inserted into from access.
You are trying to fire a trigger by inserting into an access table.
If your table was in sql server and you had a trigger on it and access inserted into that table via a linked table then the trigger would fire.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[ESquared]

Why not put the table in SQL server to begin with, and have Access simply link to it?