i have 2 server named A and B
in A server have database server and B have database server
in A have database named A1 with table TA1 and in B have database named B1 with table TB1
i want if i insert data into database A1 table TA1 in server A, database B1 table TB1 in server B will insert the same data to
how can do that with trigger or other ways
thx u
Hi, does anyone know what syntax I have to use if I want to use a trigger to insert into a different database. This is the code I have I'm just not sure how to get it to identify a different database.
CREATE TRIGGER History_replication ON [dbo].[History]
FOR INSERT
AS
INSERT [dbo].[History] (this one shoul dbe on the History_copy db)
(workdone,error)
SELECT workdone,error
FROM [dbo].[History]
Thanks Ed
In oracle, I use the following script in trigger:
INSERT INTO TABLE1 (CITY, STATE) VALUES (:new.city,:new.state);
I am wondering how to insert new data in SQL server. Thanks!
I have a table called drugs with a cost, date, and drugname field. I have a history table that I want to insert into anytime the fields are changed in the drugs table. I want to capture the old data along with the new cost. is that possible in a update trigger? What I have below doesn't like when it comes to setting the new cost
create trigger Updt_drugs on drugs for update as
-- updates record with sql user and timestamp
--created 1-12-02 tim cronin
declare @newcost money
begin
set @newcost = select cost from inserted
insert into drugsold ([oldcost],[cost],[cdate],[drug])
select [cost],@newcost,[cdate],[drug]
from deleted dt
end
I have two databases one running of MS SQL Server and other on Oracle..
My requirement is such that, I want an trigger to be written on table in MS SQL Server such that it inserts / updates values of tables in Oracle Database tooo..is there any methodology for the same..
regards.
hello,
need help with a simple trigger i have been working on. the trigger automatically sends me an email out when a record is inserted, how ever i can't seem to get the row column data into the email. The part i do not understand is that I get the row column data information in the email if I update the row.
This is for 2005 SQL
Any direction would be greatly appreaciated
OneIDesigned
I created a link from Oracle to SQL server.
There is a trigger with insert statment in Oracle side.
I got error message as follow when trigger is invoked:
SQL> insert into test1 values ('zerbra','brazi');
insert into test1 values ('zerbra','brazi')
*
ERROR at line 1:
ORA-02047: cannot join the distributed transaction in progress
ORA-06512: at "GGWEB.TRIGGER2", line 4
ORA-04088: error during execution of trigger 'GGWEB.TRIGGER2'
my simple trigger is as follows:
create or replace
TRIGGER TESTRI
AFTER INSERT ON TEST1
FOR EACH ROW
BEGIN
insert into test1@sqlserver (city,state) values ('what','nine');
END;
I've never worked with a column that's defined as a binary data type. (In the past if ever we had to include a photo we stored a link to the JPG/BMP/PNG/whatever into a column, but never actually inserted or updated a column with binary data.But now I've got to do that. So how do you put data into a column defined as BINARY(4096)?
View 3 Replies View RelatedI have trigger, but not execute somedata because insert few row in every second. I use java to insert data to SQL server 2005. Data inserted to a table but not executing trigger for some data.
For example 100 data every second inserted to a table.
If insert data one by one to a table trigger fires success.
Please Help me.
Hi
I am Importing data in datatable using SSIS package . I made trigger on that table on insert. The trigger on insert is not firing on that table
Please help
Thanks
CP
I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT
set @startdate = '2015-01-01'
set @enddate = '2015-01-31'
[Code] .....
I have two VB.NET dataclient program A abd B.
What i want to do is .. to find the best way to insert the high speed data( that comes at every 10 ms) to the sql server express database table .
I have two options to store the data in the table . One is to insert all the data in one row , the other is to divide it into many rows in the same table . In approach A i will get like 10 records per second and in approach B i may get i may get 30 - 50 records per second based on the amount of data that is comming . i.e i am creating a new row in the table for every extra column added in Approach A and duplication the other columns.
I wanted to find the better way to insert the data based on the performace metrics like CPU usage and memory usage.
I'm trying to insert a record into a table and I'm finding that I can only insert the primary key. When I try to insert only another field only it does not happen which makes me think the primary key is not being auto-created and I think that I have to set up a trigger to do thisExample:<CODE> oConn.ExecuteMySQL("INSERT INTO tblUsers(strUserID,strName)VALUES(45,'x')", True)</CODE>.The code above works well, but I want to auto-generate a trigger that will insert a unique strUserID (primary Key) each time a user does an insert so that I dont have to specifically indicate strUserID.<CODE> oConn.ExecuteMySQL("INSERT INTO tblUsers(strName)VALUES('x')", True)</CODE>The code above does not work well because it does not create a strUserID. How do I create a trigger that will auto-insert a primary key into strUserID?
Thanks.Joe
create or replace
trigger trgRNR_Budget before insert on tblRNR_Budgets
for each row
when (new.BUDGETID is null)
begin
select RNR_BID.nextval into :new.BUDGETID from dual;
end;
/
How to write above before insert trigger for each row in sql server...
Good morning all!
Let us suppose I have a table (tblInfo) with 4 fields. PK, SSN, Date_Created, and Status. Let us assume there are currently 4 records in the field, 3 with the same SSN. Of those 3, the status of 2 are 'inactive' and the other is 'active'.
When I insert a new record into tblInfo, with the same SSN as the other 3, I need the status of those 3 to change to 'inactive' (or stay as inactive if already set) leaving the 4th record with a different SSN alone.
I know I need an insert trigger, but not sure on the actual code of it.
Hello,I am learning SQL Server 2005.I need to create a trigger which increments number of book'spublications:CREATE TRIGGER InsertPublicationON PublicationsAFTER INSERTASBEGINSET NOCOUNT ON;DECLARE @Num smallintSET @Num = SELECT NumPublications FROM Books WHERE ISBN IN(SELECT ISBN FROM inserted);UPDATE BooksSET NumPublications = @Num + 1WHERE ISBN IN(SELECT ISBN FROM inserted);ENDUnfortunately I receive a message:Incorrect syntax near the keyword 'SELECT'.Could you explain me please how to correct the code?I am new to SQL Server.Thank you very much./RAM/
View 2 Replies View RelatedI'm writing an insert trigger in one SQL Server database that is supposed to insert another record into a linked SQL Server database. I have the linked server set up and have been using it for a few weeks in queries and stored procedure with no problem. Now that I'm trying to use it within a trigger and it just bombs.
I'm getting the following message in one of my logs and I don't know what it means... "Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE". I've googled around, but can't really find anything. Any help would be appreciated.
Thanks,
Bryan
Hei,We have 2 MS SQL SERVER 2000 installed on 2 different servers (2 separatedmachines).I am triing to connect them så that when one row is added to the table inthe database in main server - then the same row is added to the same tablein the second server database.I made the insert trigger on the table in the first server ( the secondserver is added as a linked server):-----------------------------------------------------------------------------------------create trigger ti_myTabe1 on myTable1 for insert asbegindeclare ........BEGINinsert into server2.myDatabase2.owner.myTable2(column1, column2, column3)SELECT column1, column2, column3FROM inserted insEND......end-----------------------------------------------------------------------------------------When I run the statement in "SQL Query Analyzer"on the first server:insert into Table1 values(va1,val2,val3)then error is coming:Server: Msg 7391, Level 16, State 1, Procedure ti_myTabe1 , Line 19The operation could not be performed because the OLE DB provider 'SQLOLEDB'was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in thespecified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a].The straing thing is: if I run the statement in "SQL Query Analyzer"on thefirst server:insert into server2.myDatabase2.owner.myTable2 values(va1,val2,val3)then it works!But not inside the trigger!!! - What I am doing wrong ?Any idea is greatly appeciated.
View 2 Replies View RelatedTable 1
First_Name
Middle_Name
Surname
John
Ian
Lennon
Mike
Buffalo
Tyson
Tom
Finney
Jones
Table 2
ID
F
M
S
DOB
1
Athony
Harold
Wilson
24/4/67
2
Margaret
Betty
Thathcer
1/1/1808
3
John
Ian
Lennon
2/2/1979
4
Mike
Buffalo
Tyson
3/4/04
5
Tom
Finney
Jones
1/1/2000
I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.
A
ID
Name
1
Joe
2
Fred
3
Ian
4
Bill
B
ID
1
4
I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?
I believe the trigger should look something like this:
create trigger test_trigger
on a
for insert
as
insert into b(ID)
select i.id
from inserted i
where
--specific USER
I have created an insert trigger to update an address column with a default when it is null, for different clients using a case statement.
When I import data using the import wizard, the trigger doesn't fire.
Doesn't SQL see an import as an insert?
How can I get this trigger to fire for an import?
What statement do I use, as part of an insert trigger, to insert xml data from the xml database to a flat file database, to check if a record with a specific ID exists to delete first then insert the changed record, instead of adding the changes or an updated from the original xml database.
What I’m trying to do is take the xml formatted data out of one sql server database and insert the data only in that xml into a another sql database. So I can play with the data.
Problem is if the data in the xml is updated or changed for a specific record on the original xml database then the trigger inserts another copy into the created database (which I don’t want).
This is on SQL Server 2008R2.
We have created a DDL trigger on SQL server 2005 database for DB audit purpose. Following is the script used for trigger creation
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ChangeLog](
[Code] ....
After the DDL trigger creation. Application team started reporting following error while executing a stored procedure.
*********************************
Error 1:
INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
Error2:
[Execute SQL Task] Error: Executing the query "exec sp_drop_indexes_EnhLeaseData delete from dbo.leases where vin_num='XXX' and lease_acct_num='XXXX' delete from dbo.leases where vin_num='XXX' and lease_acct_num='080066225' " failed with the following error: "INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I create a Trigger that allows to create news row on other table.
ALTER TRIGGER [dbo].[TI_Creation_Contact_dansSLX]
ON [dbo].[_IMPORT_FILES_CONTACTS]
AFTER INSERT
AS
[code]...
But if I create an INSERT with 50 rows.. My table CONTACT and ADDRESS possess just one line.I try to create a Cursor.. but I had 50 lines with an AdressID and a ContactID differently, but an Account and an AccountId egual on my CONTACT table :
C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC001 - ACCOUNT 001
C003 - AD003 - AC001 - ACCOUNT 001
C004 - AD004 - AC001 - ACCOUNT 001
C005 - AD005 - AC001 - ACCOUNT 001
I search a means to have 50 lines differently on my CONTACT table.
C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC002 - ACCOUNT 002
C003 - AD003 - AC003 - ACCOUNT 003
C004 - AD004 - AC004 - ACCOUNT 004
C005 - AD005 - AC005 - ACCOUNT 005
I have a master table with after insert trigger on it.. When record is inserted into master table, the trigger fires and is captured in the backoffice table. In case the trigger fails, my record is neither in the master table nor in the back office table..
Is there anyway to capture the record either in the master table or in a separate table.
I have two tables. Order table and order detail table.
What I want to do is send notification to a service when an order is created. And I want to include both header and detail of the order.
I can't get working with on insert trigger . How to go around with trigger ?
I have a problem described as follows: I have a table with one instead of insert trigger:
create table TMessage (ID int identity(1,1), dscp varchar(50))
GO
Alter trigger tr_tmessage on tmessage
instead of insert
as
--Set NoCount On
insert into tmessage
[code]....
When I execute P1 it returns 0 for Id field of @T1.
How can I get the Identity in this case?
PS: I can not use Ident_Current or @@identity as the table insertion hit is very high and can be done concurrently.Also there are some more insertion into different tables in the trigger code, so can not use @@identity either.
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted
END
/************************************************
3) - Do the insert
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)
select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************
4) - Drop the trigger
************************************************/
drop trigger [dbo].[trig_my_table__instead_insert]
go
/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK
I have two different SQL 2008 servers, I don't have permission to create a linked server in any of them. i created a trigger on server1.table1 to insert the same record to the remote server server2.table1 using OPENROWSET
i created a stored procedure to insert this record, and i have no issue when i execute the stored procedure. it insert the recored into the remote server.
The problem is when i call the stored procedure from trigger, i get an error message.
Stored Procedure:
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
When i try to insert a new description value in the table i got the following error message:
No row was updated
the data in row 1 was not committed
Error source .Net SqlClient Data provider.
Error Message: the operation could not be performed because OLE DB
provider "SQLNCLI10" for linked server "(null)" returned message "The partner transaction manager has disabled its support for remote/network transaction.".
correct the errors entry or press ESC to cancel the change(s).
Hi
I am trying to use multiple insert for a table T1 to add multiple rows.
Ti has trigger for insert to add or update multiple rows in Table T2.
When I provide multiple insert SQL then only first insert works while rest insert statements does not work
Anybody have any idea about why only one insert works for T1
Thanks
Hi,
I have two SQL Express database and I want to do two things. One is to transfer a table over to the other database. Two, move the files from one table in one database to another. Please let me know when you get a chance.
Thanks,
Kyle
I have a situation where I have Table A, Table B.
View C is created by joining table A and table B.
I have written a instead of trigger D on view C.
I do not insert/update/delete on the view directly.
For every insert/update in table A /B the values should get insert/update in the view respectively. This insert/update on view should invoke the trigger.
And I am unable to see this trigger work on the view if any insert/update occurs on base table level.
Trigger is working only if any operation is done directly on the view.
What is wrong with this code the dropdowlist mainlyusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;public partial class NewAccount : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { } protected void NaccountButton_Click(object sender, EventArgs e) { if (Page.IsValid) { //Define data objects SqlConnection conn; SqlCommand comm; //read from web config string connectionString = ConfigurationManager.ConnectionStrings["OneBank"].ConnectionString; //Initialize connection conn = new SqlConnection(connectionString); //create command comm =new SqlCommand( "INSERT INTO Customer (FirstName, LastName, Street, City, State," + "Zip, Phone, Payee,AccountType)" + "VALUES(FirstName, LastName, Street, City," + "State, Zip, Phone, Payee,AccountType)", conn); //add parameters comm.Parameters.Add("@FirstName", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@FirstName"].Value=Firstname.Text; comm.Parameters.Add("@LastName", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@LastName"].Value=lastname.Text; comm.Parameters.Add("@Street", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@Street"].Value=street.Text; comm.Parameters.Add("@City", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@City"].Value=city.Text; comm.Parameters.Add("@State", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@State"].Value=state.Text; comm.Parameters.Add("@Phone", System.Data.SqlDbType.Int); comm.Parameters["@Phone"].Value=phone.Text; comm.Parameters.Add("@AccountType", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["AccountType"].Value = dropdownlist1.SelectedValue.ToString(); //Enclose database in try catc finally try { //open connection conn.Open(); //execute the command comm.ExecuteNonQuery(); //reload query Response.Redirect("NewAccount.aspx"); } catch { //Display error Errormessage.Text= "Error submitting request!"; } finally { conn.Close(); } } }}
This is the table where thisis going
customer ------------- customerID pk generated automatically firtsname lastname street city statezipphoneaccountType