.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Santhakumar Munuswamy
Asad Ali
Fauzul Azmi
Post New Web Links

MS Sync Framework INSERT Trigger

Posted By:      Posted Date: September 14, 2010    Points: 0   Category :.NET Framework
 
Hello, Due to the sync framework insert trigger my business logic was broke. I was getting resulted in a StaleStateException (-1 rows affected, expected 1). error. I looked at the sync insert trigger and found a strange thing. There is an update call before inserting to the tracking table. This update call joins with the tracking table for inserted record. How it is going update the record which really do not exists! I removed the UPDATE call and it works fine now. However I am not sure the reason behind the presence of the UPDATE call withing the INSERT trigger. Here is the sample trigger: ALTER TRIGGER MYTABLE_insert_trigger] ON MYTABLE FOR INSERT AS UPDATE [side] SET [sync_row_is_tombstone] = 0, [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE(), [ID] = [i].[ID] FROM [sync].[MYTABLE_tracking] [side] JOIN INSERTED [i] ON [side].[PrimaryKey1] = [i].[PrimaryKey1] AND [side].[PrimaryKey2] = [i].[PrimaryKey2] INSERT INTO [sync].[MYTABLE_tracking] ([i].[PrimaryKey1], [i].[PrimaryKey2], [create_scope_local_id], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime], [restore_timestamp], [i].[ID]) SELECT [i].[PrimaryKey1], [i].[PrimaryKey2], NULL, 0, @@DBTS+1, NULL, 0, 0, GETDATE(), NULL, [i].[ID] FR


View Complete Post


More Related Resource Links

In Sync: Creating Synchronization Providers with the Sync Framework

  

The Sync Framework can be used to build apps that synchronize data from any data store using any protocol over a network. We'll show you how it works and get you started building a custom sync provider.

Joydip Kanjilal

MSDN Magazine August 2010


Sync Up: Manage Your Data Effectively With The Microsoft Sync Framework

  

Synchronization Services for ADO.NET provide a set of tools to help you synchronize data between two database sources, synchronize files between machines, and synchronize with an RSS or ATOM feed. Learn more here.

James Yip

MSDN Magazine April 2009


How to add Microsoft Sync Framework to Prerequisites Dialog Box

  
Hello everyone. I created a setup project in Visual Studio 2008 and I need to add microsoft sync framework as a prerequisite. But microsoft sync framework option is not in the Prerequisites Dialog Box. How can I add microsoft sync framework as a prerequisite to my project?

After insert trigger on the same table.

  
Hi! i have the following table. CREATE TABLE [dbo].[EICANALESRES]( [IdContrato] [decimal](10, 0) NOT NULL, [EiCanalId] [decimal](10, 0) NOT NULL, [EiFechServ] [datetime] NOT NULL, [EiFechMaq] [datetime] NOT NULL, [EiFechCorr] [datetime] NOT NULL, [EiValorFis] [decimal](25, 4) NOT NULL, [EiValorNum] [decimal](25, 4) NOT NULL, [EiValorCalc] [decimal](25, 4) NOT NULL, [EiEst] [smallint] NOT NULL, PRIMARY KEY CLUSTERED ( [IdContrato] ASC, [EiCanalId] ASC, [EiFechServ] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]  I want a new record that takes all the data from from the recently inserted record where idcontrato =2 and eicanalid =91. And then give this new record a new idcontrato. Without deleting the original record. i have this so far, but it erases the original record.   alter TRIGGER [dbo].[datosdemo] ON [dbo].[EICANALESRES] AFTER INSERT AS BEGIN SET NOCOUNT ON; delete from eicanalesres where idcontrato = '13' and eicanalid = '91' and eivalorfis = '0' update dbo.eicanalesres SET idcontrato = '13' FROM INSERTED WHERE eicanalesres.idcontrato = inserted.idcontrato AND eicanalesres.eicanalid = inserted.eicanalid and eicanalesres.eifechserv = inserted.eifechserv and inserted.idcontrato = '02' and inserted.eicanalid = '91'

After INSERT Trigger question - how to use value from last added record

  
Hi, I have a table where one of the fields is defined like [barCodeID] int IDENTITY(1, 1) NOT NULL - so the value in this column is generated automatically. What I want is, after each INSERT, to write this automatically generated value to another table. The simplest would be to do it with an after insert trigger, but don't know how. How to refer to a value from the last added record in a trigger? Thanks

how can insert only new record into another table through trigger

  
I have on master table in sql in which every time new data is inserted.i just want to copy the new inserted record into another table.How can i do this. below is my trigger but how can it identify only new records should be inserted into second table.please help me and modify it. CREATE TRIGGER CopyRecordToPortInTable ON SmsTable AFTER INSERT AS BEGIN SET NOCOUNT ON; insert into Portintable(smsno,smstext,flag) select (sms,smstext,0) where condition END

Trigger without UPdate,INSERT and DELETE

  

I have SQL SERVER 2005 and I have a table with a Cloumn DATETIME data Type,

I want to ceate a trigger that checks the table rows and delete the record (s) when it has (DATETIME colume value - today date()) >20  days.

Is it possible to write a trigger for that? 

I am new to SQL Server and trigger.

Thanks,

MK 

 


calling a trigger after insert sp

  

i want  to create a trigger after i insert record in table1 and in trigger there will be an update sentece which updates x column in table2 

so

i have a stored procedure like

insert into Table1(a,b,c) values(1,2,3)

select @@identity

then

my trigger should update table 2

update table2 set x=5 where id=@a

@a parameter must come from my insert stored procedure's select @@identity line

 

how can i create a trigger like this? this will be my first trigger so i dont know anything about them

after creating trigger i think i should control if it execute successfully or not in my insert stored procedure so if it works successfully my insert is ok but if trigger doesnt work my insert sentence shouldnt work

 

could anyone help me about that?

 

thanks

 


Insert Muiple rows Into Table using Entity Framework ??

  

Dear All,

 

i'm trying to use Entity Framework to insert into SQL SERVER 2005, using Visual studio 2008.

the following codes are a Button onclick event,

I want to insert into DB multiple rows at a time

protected void Button1_Click(object sender, EventArgs e)
    {
        MyTable NewRow = new MyTable();

        NewRow.columnName = "5";  //columnName is a primary key
        DbEntity.AddToAllowRevise(NewRow); 
        DbEntity.SaveChanges();

        NewRow.columnName = "6";
        DbEntity.AddToAllowRevise(NewRow);
        DbEntity.SaveChanges();
    }


however this does not work,

it seems we can only insert into DB only one row at a time (??)

if i write :

MyTable NewRow1 = new MyTable();

NewRow1.columnName = "5";

 

MyTable NewRow2 = new MyTable();

NewRow2.columnName = "6";

 

MyTable NewRow3 = new MyTable();

NewRow3.columnName = "7";

....

MyTable NewRow10 = new MyTable();

it will success.

but what if i have more rows, like 20~30 row? i have to seperately write 20~30

MyTable NewRow20 = new MyTable();  ........... MyTable NewRow30 = new MyTable();

 

?? sorry i'm a beginn

In a trigger can up insert data into a table located on a linked server?

  

EXEC master.dbo.sp_addlinkedserver @server = N'ServerB', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerB',@useself=N'False',@locallogin=NULL,@rmtuser=N'lnklogin',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'query timeout', @optvalue=N'0'

SQL Trigger INSERT, UPDATE table name (inserted updated)

  
I am trying to make a trigger for INSERT, UPDATE.  For INSERT you get a table named inserted that you can use, and for UPDATE you get updated.  But what is the name if you doing a trigger for both?

multiple insert rows using Entity Framework

  

Dear All,

 

i'm trying to use Entity Framework to insert into SQL SERVER 2005, using Visual studio 2008.

the following codes are a Button onclick event,

I want to insert into DB multiple rows at a time

protected void Button1_Click(object sender, EventArgs e) 
{ 
	MyTable NewRow = new MyTable(); 
	NewRow.columnName = "5"; //columnName is a primary key
	DbEntity.AddToAllowRevise(NewRow); 
	DbEntity.SaveChanges(); 
	
	NewRow.columnName = "6"; 
	DbEntity.AddToAllowRevise(NewRow); 
	DbEntity.SaveChanges(); 
} 


 

however this does not work,

it seems we can only insert into DB only one row at a time (??)

if i write :

MyTable NewRow1 = new MyTable();

NewRow1.columnName = "5";

 

MyTable NewRow2 = new MyTable();

NewRow2.columnName = "6";

 

MyTable NewRow3 = new MyTable();

NewRow3.columnName = "7";

....

MyTable NewRow10 = new MyTable();

it will success.

but what if i have more rows, like 20~30 rows? i have to seperately write 20~30

MyTable NewRow20 = new MyTable();  ........... MyTable NewRow30 = new MyTable();

 

?? sorry i'm a beginner now, please anybody can help? better idea?<

Sync Framework 2.1 The modified data Cannot synchronized

  
In the server database table delete and add new data can be downloaded to the client but the table has a field on a data modification cannot be synchronized to the client

DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("testScope");
  DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tab, serverConn);
  scopeDesc.Tables.Add(tableDesc);
  SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
  serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
  serverProvision.Apply();

DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("testScope"), serverConn);
  SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);
  clientProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
  clientProvision.Apply();
 


How to insert foreignkey in Entity framework in MVC

  

I m try to insert record to table, its working well without foreignkey using Entity model. But i need to insert foreign key. I used the following query, its not working well via Entity model.

objFacility.FacilityCategoryReference.EntityKey =

new System.Data.EntityKey("testEntities.test", "id", 10);

What i did wrong? or anything missed in my code. Please share your points

Thanks

Alagar.M


trigger on insert is failing for data insert in a table

  

hello experts,

here is some question for you and i'm sure you guys will have awsome solution

i have a table with companyid and tranid as a primary key. there is a insert trigger on it and the trigger insert property is instead of insert, means data is manually inserted thru trigger.

in trigger, i check if companyid and tran id exists in the table or not, if yes then i add a record in another table (history table/duplicate table) and if not found then i add record in this table.

this stuff work perfectly if i work in ssms and it is all good but when i work thru ssis, it gives me following error and it clearly showing that it is because of pk

[Insert Data in EFT [46]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PK_Mail_Queue'. Cannot insert duplicate key in object 'dbo.EMail_Queue'.".

any solution


mark it as answer if it answered your question :)

the insert trigger works properly, the delete trigger does not.....what do you think

  

INSERT TRIGGER

ALTER TRIGGER [dbo].[CREER_SOMMAIRE_PARTIE_JOUEUR] ON [dbo].[SOMMAIRE_PARTIE]

FOR INSERT
  as

DECLARE     @NB_BUT int,
            @RESULTAT_VISITEUR int,
            @ID_EQUIPE_VISITEUR int,
            @ID_EQUIPE_LOCAL int,
            @ID_EQUIPE_JOUEUR int,
            @RESULTAT_LOCAL int,
            @NB_PASSE int,
            @NB_MINUTE_PUNITION int,
            @NB_BUT_TOTAL_JOUEUR int,
            @NB_PASSE_TOTAL_JOUEUR int,
            @NB_MINUTE_PUNITION_JOUEUR int


/* Sélectionner le nombre de but inscrit par un joueur */
SELECT @NB_BUT = NB_BUT
FROM inserted SOMMAIRE_PARTIE

/* Sélectionner le résultat avant tout changement de l'équipe visiteur */
SELECT @RESULTAT_VISITEUR = RESULTAT_VISITEUR
FROM PARTIE
    join SOM

Multiple database support with Entity Framework

  
One of the features introduced in Entity Framework is being database independent. Which mean each database provider can support Entity Framework by implementing its provider.

This feature allows you build applications independent from the underplaying database provider. In this post I'm going to show how to build an application that support multiple databases using same conceptual model made by Entity Framework.
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend