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

  • Thread starter Thread starter youssef
  • Start date Start date
Y

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.
 
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?
 
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 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



david epsom dot com dot au said:
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 said:
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.
Click to expand...
Click to expand...
 
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)


youssef said:
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



david epsom dot com dot au said:
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 said:
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
Click to expand...

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

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

Is anyone can help me about that.
Click to expand...
Click to expand...
Click to expand...
 
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


david epsom dot com dot au said:
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)


youssef said:
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 :
Click to expand...
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



david epsom dot com dot au said:
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
Click to expand...

-------------------------------------------------------------------------- in
the
ACCESS DB.

Is anyone can help me about that.
Click to expand...
Click to expand...
Click to expand...
 
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)



youssef said:
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


david epsom dot com dot au said:
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
Click to expand...
record
in
the second table? Where is the second table? What is the table name?
Click to expand...
What
is
the name of the database?

(david)
Click to expand...
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
Click to expand...
in
Log
INSTEAD OF INSERT
AS
BEGIN

My code

END
Click to expand...

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

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

Is anyone can help me about that.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top