Wednesday 15 April 2015

TSQL- Trigger instead of UPDATE column not included in update statment -


I have a small problem I'm trying to do. But I suspect that this is not possible and just want some other insights.

I have a table with six columns, two of which are related to the update recording on the table, 'updateby' and 'update'. When a person applies an update to the table, the date of the update is set to a system time, even if a person tries to decide the time and date (there is no problem here), but when the 'UpdateB' I have tried to use the 'insert' table from within the trigger, but when a statement is executed, I have a problem.

  Set the Update Pool Group PoolDescription = 'test1 description' where poolName = 'test 2'   

Any entry for that 'update' column Does not occur, which was passed in the 'Updated.based' field already using the 'Update' statement that is in the table. But the original origin of the changes in 'Inserted.PoolDescription' is not triggered here.

  TRIGGER TR_PoolGroups_Update Create update instead of PoolGroups as the Update PoolGroups SET PoolDescription = i.PoolDescription, UpdatedBy = Insert (i.updatedby, (select SUSER_NAME ()), UpdatedDate = GetDate Join PoolGroups PG Interiors Join i.PoolName = pg.PoolName End GO   

The data is already in table PoolGroups.

  PoolName Pool Description Updated Updated Test 2 Test Details Test1 2013-06-22 14: 39: 55.930   

Trigger at the time of inserting Data in the table inserted in

  PoolName PoolDescription UpdatedBy UpdatedDate Test 2 Test 1 Description test1 2013-06-22 14: 39: 55.930   

I just catch the update statement when ' UpdatedBy 'field was not included and then replace it with logged in user name. My problem is that I can not work as to know how it was not included in this field or update statement that is running against this table because I hope the 'UpdatedBy' field will contain an inserted table for the empty space Is there any other way I can know whether this update is specified in the update or not?

I think I have found my answer. With the use of the 'Update' column, I can determine whether the problem with me has been updated in the update statement or not. Therefore, with just one case statement, my problem has been solved by changing the 'Collage' statement. New Trigger Code

  create TRIGGER TR_PoolGroups_Update Update instead of PoolGroups as updated Update PoolGroups SET PoolDescription = i.PoolDescription, UpdatedBy = when update (UpdatedBy) then select i.updatedby rest (SUSER_NAME) Join the PoolGroups Postgraduate Interiors ()) from the end, UpdatedDate = getdate () I go to i.PoolName = pg.PoolName end   

Now apply a update to any table they do not have By default, 'updateb' must be specified by default, it is now pickup to SQL username Will up.

No comments:

Post a Comment