I have a C# web using EF6 to update data. and a SQL Server trigger will be triggered during update. The trigger is supposed to find the updated column by
SELECT
@Columns_Updated = ISNULL(@Columns_Updated + ',', '') + name
FROM
syscolumns
WHERE
id = @idTable
AND CONVERT(VARBINARY, REVERSE(COLUMNS_UPDATED())) & POWER(CONVERT(BIGINT, 2), colorder - 1) > 0
I have a question in below statement
CrmClientContact t1 = dbdb.CrmClientContact.Where(x => x.rowId == 4).FirstOrDefault();
t1.updatedAt = DateTime.Now;
dbdb.Entry(t1).State = EntityState.Modified;
dbdb.SaveChanges();
CrmClientBetLimit t2 = dbdb.CrmClientBetLimit.Where(x => x.rowId == 1028).FirstOrDefault();
t2.updatedAt = DateTime.Now;
dbdb.Entry(t2).State = EntityState.Modified;
dbdb.SaveChanges();
CrmClientCLState t3 = dbdb.CrmClientCLState.Where(x => x.rowId==1).FirstOrDefault();
t3.updatedAt = DateTime.Now;
dbdb.Entry(t3).State = EntityState.Modified;
dbdb.SaveChanges();
This is what I usually doing to update records in the EF6 framework.
As shown I have updated the field updatedAt in those 3 tables.
However, the trigger showed completely different @Columns_Updated
Table 1: clientId, createdAt, createdBy, name, rowId for t1 (it has 11 columns in the table)
Table 2: betLimitValueSum, clientId, createdAt, createdBy, currId, updatedAt, updatedBy for t2 (it has 8 columns in the table)
Table 3. rowId for t3 (it has 9 columns in the table)
I cannot find the reason why they return these columns.
Note: both tables contain columns createdAt, createdBy, updatedAt, updatedBy
To solve the issue, I tried to remove these statements from the code
dbdb.Entry(t1).State = EntityState.Modified;
dbdb.Entry(t2).State = EntityState.Modified;
dbdb.Entry(t3).State = EntityState.Modified;
Thus, I think the problem is attaching the EntityState before db.SaveChange().
I want to know
why attached the
EntityStatewould cause those unexpected update columns to occur in the datatable?when I should attach the
EntityState.Modified(I thought it is proper to do it when updating record like the above code, but obviously the trigger shows its not)?
-----------------Updated----------------- After removed EntityState.Modified from the code. The trigger is still unable to receive correct @UpdatedColumns, I have tried the below code
t3 = dbdb.CrmClientContact.Where(x => x.rowId == 7).FirstOrDefault();
t3.updatedAt = DateTime.Now;
t3.updatedBy = DateTime.Now.ToString();
t3.mobile = DateTime.Now.ToString();
t3.name = DateTime.Now.ToString();
dbdb.SaveChanges();
However, in the trigger it return createdAt,rowId as the updated columns. Note that some of tables could worked.
And the behavior is completed unexpected for me. For examples
CrmClientContact t3 = dbdb.CrmClientContact.Where(x => x.rowId == 5).FirstOrDefault();
t3.updatedAt = DateTime.Now;
t3.email = DateTime.Now.ToString();
dbdb.SaveChanges(); // correct
t3 = dbdb.CrmClientContact.Where(x => x.rowId == 5).FirstOrDefault();
t3.updatedAt = DateTime.Now.AddDays(1);
t3.email = DateTime.Now.ToString() + 1;
dbdb.SaveChanges(); //correct
t3 = dbdb.CrmClientContact.Where(x => x.rowId == 5).FirstOrDefault();
t3.updatedAt = DateTime.Now.AddDays(2);
t3.email = DateTime.Now.ToString() + 2;
t3.mobile = DateTime.Now.ToString() + 2;
t3.name = DateTime.Now.ToString() + 2;
dbdb.SaveChanges(); // correct
t3 = dbdb.CrmClientContact.Where(x => x.rowId == 5).FirstOrDefault();
t3.updatedAt = DateTime.Now;
t3.updatedBy = DateTime.Now.ToString();
dbdb.SaveChanges(); // correct
t3 = dbdb.CrmClientContact.Where(x => x.rowId == 5).FirstOrDefault();
t3.updatedAt = DateTime.Now.AddDays(1);
t3.updatedBy = DateTime.Now.ToString()+1;
dbdb.SaveChanges(); // correct
t3 = dbdb.CrmClientContact.Where(x => x.rowId == 5).FirstOrDefault();
t3.updatedAt = DateTime.Now.AddDays(2);
t3.updatedBy = DateTime.Now.ToString() + 2;
dbdb.SaveChanges(); // correct
t3 = dbdb.CrmClientContact.Where(x => x.rowId == 5).FirstOrDefault();
t3.updatedBy =3+ DateTime.Now.ToString();
t3.email =1+ DateTime.Now.ToString();
dbdb.SaveChanges();//null, incorrect
t3 = dbdb.CrmClientContact.Where(x => x.rowId == 5).FirstOrDefault();
t3.updatedBy =2+ DateTime.Now.ToString();
t3.email =1+ DateTime.Now.ToString();
dbdb.SaveChanges();//updatedBy, incorrect (email is missing)
t3 = dbdb.CrmClientContact.Where(x => x.rowId == 5).FirstOrDefault();
t3.updatedBy = 22 + DateTime.Now.ToString();
t3.email = 11 + DateTime.Now.ToString();
dbdb.SaveChanges();//null, incorrect
t3 = dbdb.CrmClientContact.Where(x => x.rowId == 5).FirstOrDefault();
t3.updatedBy = 222 + DateTime.Now.ToString();
t3.email = 111 + DateTime.Now.ToString();
dbdb.SaveChanges();//null, incorrect
t3 = dbdb.CrmClientContact.Where(x => x.rowId == 5).FirstOrDefault();
t3.updatedBy = 22 + DateTime.Now.ToString();
t3.email = 11 + DateTime.Now.ToString();
dbdb.SaveChanges();//null, incorrect
CrmClientContact t4 = dbdb.CrmClientContact.Where(x => x.rowId == 5).FirstOrDefault();
t4.updatedBy = 2 + DateTime.Now.ToString();
t4.email = 1 + DateTime.Now.ToString();
dbdb.SaveChanges();//null, incorrect
as shown above, i completely don't understand how the above result set can be occurred (they are deterministic, and repeatable)
--------------Final Update -------------- After using the SQL profiler, I found that the SQL query of EF6 is just a normal update statement which matches with the document. And The same result can be reproduced when executing the same SQL in the management studio(cannot find the UPDATED COLUMN in the trigger).
In result, I think SQL Server Update Trigger, Get Only modified fields can only work for previous version SQL server.
At least my database (SQLSERVER 2014 (120)) couldn't apply the marked answer to finding the updated columns.
Finally I have applied another one which pivot the deleted and inserted table and finding the different, there is no reason to fail anymore except it needed an unchanged column in the table (fortunately all my tables has primary key which is supposed unchanged).
The only issue is maintenance the trigger for each table modification.
Thanks.
