TRIGGER a VIEW with SQL for a LINKED SERVER (ACCESS DB)

[youssef]

Hi,
I have an ACCESS DB that I would like to import to a SQL DB.
I linked the ACCESS DB to my SQL SERVER.
For this, no problem.
After that I create a VIEW from my SQL DATABASE and I can see all RECORDS
from the ACCESS TABLE.

Now , I am looking for updating my Table from SQL DB by all new records
inserted in the ACCESS DB.

I create a TRIGGER from my VIEW. You can see below the source code :




CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in Log
INSTEAD OF INSERT
AS
BEGIN

My code

END
----------------------------------------------------------------------------
-----------------------------

I can execute my TRIGGER but I cannot see the new records inserted in the
ACCESS DB.

Is anyone can help me about that.

 

[Albert D. Kallal]

Does the linked table have both a primary key AND ALSO a timestamp field
exposed?

I would make sure of the above, and then delete/re-crease the link in
ms-access for the problem table. Likely when you created this table you did
not select/set a primary key.

Also, are you using a native sql project in ms-access (ADP project), or
odbc?

 

[david epsom dot com dot au]

Dunno. Will SQL Server trigger on events on foreign tables?
I don't think there is anyone here who has ever tried it.

(david)

 

[youssef]

Hi,

I selected the primary key.

I find in a forum a solution but I don't understand how it is working.

Please find the link :

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

With SQL :
http://www.experts-exchange.com/Databases/MS_Access/Q_20823443.html

Can you help me about that ?



Best Regards

Dunno. Will SQL Server trigger on events on foreign tables?
I don't think there is anyone here who has ever tried it.

(david)

Hi,
I have an ACCESS DB that I would like to import to a SQL DB.
I linked the ACCESS DB to my SQL SERVER.
For this, no problem.
After that I create a VIEW from my SQL DATABASE and I can see all RECORDS
from the ACCESS TABLE.

Now , I am looking for updating my Table from SQL DB by all new records
inserted in the ACCESS DB.

I create a TRIGGER from my VIEW. You can see below the source code :




CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in Log
INSTEAD OF INSERT
AS
BEGIN

My code

END
--------------------------------------------------------------------------
--
-----------------------------

I can execute my TRIGGER but I cannot see the new records inserted in the
ACCESS DB.

Is anyone can help me about that.

 

[david epsom dot com dot au]

With Oracle:
He has an Oracle table.

With SQL:
He has an SQL Server table.

Do you have a SQL Server table? What is the table name? What is the name
of the database?

Does the trigger fire when you do an insert? Does it insert a new record in
the second table? Where is the second table? What is the table name? What is
the name of the database?

(david)

Hi,

I selected the primary key.

I find in a forum a solution but I don't understand how it is working.

Please find the link :

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

With SQL :
http://www.experts-exchange.com/Databases/MS_Access/Q_20823443.html

Can you help me about that ?



Best Regards

Dunno. Will SQL Server trigger on events on foreign tables?
I don't think there is anyone here who has ever tried it.

(david)

Hi,
I have an ACCESS DB that I would like to import to a SQL DB.
I linked the ACCESS DB to my SQL SERVER.
For this, no problem.
After that I create a VIEW from my SQL DATABASE and I can see all RECORDS
from the ACCESS TABLE.

Now , I am looking for updating my Table from SQL DB by all new records
inserted in the ACCESS DB.

I create a TRIGGER from my VIEW. You can see below the source code :




CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in Log
INSTEAD OF INSERT
AS
BEGIN

My code

END

--------------------------------------------------------------------------

--
-----------------------------

I can execute my TRIGGER but I cannot see the new records inserted in the
ACCESS DB.

Is anyone can help me about that.

 

[youssef]

Hi,

Thank for your time.

So, I have one Table in the ACCESS DB.
This DB, I linked with SQL SERVER.
At this moment, I can connected to the ACCESS DB from my SQL SERVER (LINKED
SERVER NAME : MTX_BTS_LOG)

Now I create in my SQLSERVER a DB with the same structure TABLE than the
ACCESS DB.
the Name of the Table is : [Router Status]

In this way, I create in my SQL DB, a view (VIEW_MTX_BTS_LOG) that represent
the TABLE of the linked server( ACCESS DATABASE).
The name of the VIEW : VIEW_MTX_BTS_LOG

SELECT * FROM MTX_BTS_LOG...[Router Status]

When I exectute the VIEW, I see all records from the TABLE of my ACCESS DB.

Now, I create a TRIGGER from this VIEW with this code :


CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in Log
INSTEAD OF INSERT
AS
BEGIN

My code => Insert the new record in my SQL TABLE

END
--------------------------------------------------------------------------

Now, the TRIGGER is created but nothing when a new record is inserted in the
ACCESS DB.

Can you help me ?

Best Regards

With Oracle:
He has an Oracle table.

With SQL:
He has an SQL Server table.

Do you have a SQL Server table? What is the table name? What is the name
of the database?

Does the trigger fire when you do an insert? Does it insert a new record in
the second table? Where is the second table? What is the table name? What is
the name of the database?

(david)

Hi,

I selected the primary key.

I find in a forum a solution but I don't understand how it is working.

Please find the link :

With Oracle :

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

With SQL :
http://www.experts-exchange.com/Databases/MS_Access/Q_20823443.html

Can you help me about that ?



Best Regards

Dunno. Will SQL Server trigger on events on foreign tables?
I don't think there is anyone here who has ever tried it.

(david)

Hi,
I have an ACCESS DB that I would like to import to a SQL DB.
I linked the ACCESS DB to my SQL SERVER.
For this, no problem.
After that I create a VIEW from my SQL DATABASE and I can see all RECORDS
from the ACCESS TABLE.

Now , I am looking for updating my Table from SQL DB by all new records
inserted in the ACCESS DB.

I create a TRIGGER from my VIEW. You can see below the source code :




CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in Log
INSTEAD OF INSERT
AS
BEGIN

My code

END

-------------------------------------------------------------------------- in
the

ACCESS DB.

Is anyone can help me about that.

 

[david epsom dot com dot au]

OK, the examples you quoted are for
SQL Server tables
and
Oracle tables

You have an
ACCESS DB Table

I'm sorry, but I don't think you can use SQL Server Triggers
for an ACCESS DB Table.

However, if I am wrong, if you can use a SQL Server Trigger,
then you should try to use an
UPDATE TRIGGER
instead of INSERT TRIGGER.

It is normal to see external database inserts done as a blank insert, with a
real update.

(david)

Hi,

Thank for your time.

So, I have one Table in the ACCESS DB.
This DB, I linked with SQL SERVER.
At this moment, I can connected to the ACCESS DB from my SQL SERVER (LINKED
SERVER NAME : MTX_BTS_LOG)

Now I create in my SQLSERVER a DB with the same structure TABLE than the
ACCESS DB.
the Name of the Table is : [Router Status]

In this way, I create in my SQL DB, a view (VIEW_MTX_BTS_LOG) that represent
the TABLE of the linked server( ACCESS DATABASE).
The name of the VIEW : VIEW_MTX_BTS_LOG

SELECT * FROM MTX_BTS_LOG...[Router Status]

When I exectute the VIEW, I see all records from the TABLE of my ACCESS DB.

Now, I create a TRIGGER from this VIEW with this code :


CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in Log
INSTEAD OF INSERT
AS
BEGIN

My code => Insert the new record in my SQL TABLE

END
--------------------------------------------------------------------------

Now, the TRIGGER is created but nothing when a new record is inserted in the
ACCESS DB.

Can you help me ?

Best Regards

With Oracle:
He has an Oracle table.

With SQL:
He has an SQL Server table.

Do you have a SQL Server table? What is the table name? What is the name
of the database?

Does the trigger fire when you do an insert? Does it insert a new

record

in
the second table? Where is the second table? What is the table name?

What

is
the name of the database?

(david)

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

With SQL :
http://www.experts-exchange.com/Databases/MS_Access/Q_20823443.html

Can you help me about that ?



Best Regards



Dunno. Will SQL Server trigger on events on foreign tables?
I don't think there is anyone here who has ever tried it.

(david)

Hi,
I have an ACCESS DB that I would like to import to a SQL DB.
I linked the ACCESS DB to my SQL SERVER.
For this, no problem.
After that I create a VIEW from my SQL DATABASE and I can see all
RECORDS
from the ACCESS TABLE.

Now , I am looking for updating my Table from SQL DB by all new records
inserted in the ACCESS DB.

I create a TRIGGER from my VIEW. You can see below the source code :




CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT

in
Log

INSTEAD OF INSERT
AS
BEGIN

My code

END

--------------------------------------------------------------------------

--
-----------------------------

I can execute my TRIGGER but I cannot see the new records inserted in
the
ACCESS DB.

Is anyone can help me about that.