July 10, 2018 at 12:44 am
Hi,
How to automatically change UNIQUEIDENTIFIER column data.
ie: UNIQUEIDENTIFIER column data should be automatically change when updated any other column in the table.'
I have set NEWID() as default value for Inserting records.
Regards
Binu
July 10, 2018 at 2:24 am
binutb - Tuesday, July 10, 2018 12:44 AMHi,How to automatically change UNIQUEIDENTIFIER column data.
ie: UNIQUEIDENTIFIER column data should be automatically change when updated any other column in the table.'I have set NEWID() as default value for Inserting records.
Regards
Binu
You can do this with a trigger, here is a quick example
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_NEWID_TRIGGER') IS NOT NULL DROP TABLE dbo.TBL_NEWID_TRIGGER;
EXEC (N'CREATE TABLE dbo.TBL_NEWID_TRIGGER
(
NT_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_NEWID_TRIGGER_NT_ID PRIMARY KEY CLUSTERED
,NT_GUID UNIQUEIDENTIFIER NOT NULL CONSTRAINT DFLT_DBO_TBL_NEWID_TRIGGER_NT_GUID DEFAULT (NEWID())
,NT_INT INT NOT NULL
);');
EXEC (N'CREATE TRIGGER dbo.TRG_TBL_NEWID_TRIGGER_UPDATE_GUID
ON dbo.TBL_NEWID_TRIGGER
FOR UPDATE
AS
BEGIN
UPDATE T
SET T.NT_GUID = NEWID()
FROM INSERTED I
INNER JOIN dbo.TBL_NEWID_TRIGGER T
ON I.NT_ID = T.NT_ID
END');
--SAMPLE DATA
INSERT INTO dbo.TBL_NEWID_TRIGGER(NT_INT)
VALUES (1),(2),(3),(4),(5);
SELECT
T.NT_ID
,T.NT_GUID
,T.NT_INT
FROM dbo.TBL_NEWID_TRIGGER T;
UPDATE T
SET T.NT_INT = 10
FROM dbo.TBL_NEWID_TRIGGER T
WHERE T.NT_ID IN (1,2,5);
SELECT
T.NT_ID
,T.NT_GUID
,T.NT_INT
FROM dbo.TBL_NEWID_TRIGGER T;
Output 1NT_ID NT_GUID NT_INT
1 3E026F72-4079-4049-BA10-7CF29E630B9A 1
2 B9A54496-BDFA-47BB-AA79-BCC9A06B7C02 2
3 B3E59C85-9502-4913-A317-F97DCFD1A3F4 3
4 AD8739A3-9757-4635-A9FB-AB72653869B9 4
5 94CF4C93-E1D7-4053-8E48-125D26F872A4 5
Output 2NT_ID NT_GUID NT_INT
1 84AE3724-D29A-4C43-A406-9DED716373B1 10
2 EB4B6478-7B5C-4F3F-9F70-3F226F410F40 10
3 B3E59C85-9502-4913-A317-F97DCFD1A3F4 3
4 AD8739A3-9757-4635-A9FB-AB72653869B9 4
5 51C48C6C-F801-427F-8DD9-F551AC6DED46 10
July 10, 2018 at 6:04 am
Thanks.....
Any method without using trigger
July 10, 2018 at 6:18 am
binutb - Tuesday, July 10, 2018 6:04 AMThanks.....Any method without using trigger
Without a trigger, you'll have to add the GUID update into all update statements.
😎
July 10, 2018 at 7:07 am
🙂🙂🙂
August 9, 2018 at 11:35 pm
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply