#temp contoh

https://www.sqlservercentral.com/forums/topic/how-can-i-fire-an-update-trigger-for-each-row-when-multiple-rows-are-changed-in-one-statement

 

 

/* Sample tables and Data */

CREATE TABLE #tmpParent (

RecNum INT NOT NULL

, Info VARCHAR(10) NULL)

CREATE TABLE #tmpChild (

ParentRecNum INT NOT NULL

, ChildRecNum INT NOT NULL

, Info VARCHAR(10) NULL)

CREATE TABLE #tmpParentLog (

RecordGUID UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID())

, OrigRecNum INT NOT NULL

, Info VARCHAR(10) NULL)

CREATE TABLE #tmpChildLog (

ParentOrigRecNum INT NOT NULL

, ParentRecordGUID UNIQUEIDENTIFIER NULL

, ChildOrigRecNum INT NOT NULL

, ChildRecordGUID UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID())

, ChildInfo VARCHAR(10) NULL)

INSERT #tmpParent (RecNum, Info)

SELECT 1, 'My Data 1'

UNION SELECT 2, 'My Data 2'

UNION SELECT 3, 'My Data 3'

INSERT #tmpChild (ParentRecNum, ChildRecNum, Info)

SELECT 1,1,'My Data A'

UNION SELECT 1,2,'My Data B'

UNION SELECT 2,1,'My Data A'

UNION SELECT 2,2,'My Data B'

UNION SELECT 3,1,'My Data A'

UNION SELECT 3,2,'My Data B'

/* End Sample tables and data */

/* From here is what a trigger may look like */

CREATE TABLE #tmpOut (

RecordGUID UNIQUEIDENTIFIER NOT NULL

, OrigRecNum INT NOT NULL

, Info VARCHAR(10) NULL)

INSERT #tmpParentLog (OrigRecNum, Info)

OUTPUT INSERTED.* INTO #tmpOut

SELECT * FROM #tmpParent

INSERT #tmpChildLog (ParentOrigRecNum, ParentRecordGUID, ChildOrigRecNum, ChildInfo)

SELECT P.OrigRecNum, P.RecordGUID, C.ChildRecNum, C.Info

FROM #tmpOut P INNER JOIN #tmpChild C ON P.OrigRecNum = C.ParentRecNum

 

Tweet