August 31, 2014 at 7:48 am
Hi,
Can someone explain the purpose of below statement
create Trigger [dbo].[Trg_Generate_Data_Update]
on [dbo].[Generate_Data]
After Update
As
Begin
Update [dbo].[Generate_Data] set LastUpdateDate = GetDate()
From [dbo].[Generate_Data] inner join inserted on [Generate_Data].LastUpdateDate=inserted.LastUpdateDate
End
Go
They are creating after the trigger on Generate_Data table.
They are updating the table and setting the LastUpdateData to getdate when the table LastUpdateDate is equal to Trigger inserted column lastupdatedate
Thanks
August 31, 2014 at 7:58 am
The trigger automatically changes the LastUpdateDate column to reflect that this row has been modified.
The purpose seems to be obvious...
Usually, the column would be modified using code that actually performs the update.
But the trigger will also execute if someone performs a manual update (either using a query or edit values directly in SSMS).
However, this is no guarantee to capture all data changes. A user with permission to alter the trigger could disable it, manipulate the data an re-enable the trigger.
August 31, 2014 at 9:17 am
But on that table for the LastUpdateDate column they defined the default constraint.
Alter table [dbo].[Generate_Data] Add default (getdate()) for LastUpdateDate.
When anyone modified the existing table the default constraint will populate that column with that date.
I am some kind confusion with this.
I am thinking that they are doing same thing twice by using default constraint and triggers
August 31, 2014 at 9:43 am
The default constraint will only work on inserts not updates. The code sample below demonstrates this.
😎
USE tempdb;
GO
CREATE TABLE dbo.TBL_TEST_CONSTRAINT
(
TC_ID INT IDENTITY(1,1) NOT NULL
,TC_DATE_DEFAULT DATETIME NOT NULL CONSTRAINT DFLT_TC_DATE_DEFAULT DEFAULT(GETDATE())
,TC_TEXT VARCHAR(50) NOT NULL
);
INSERT INTO dbo.TBL_TEST_CONSTRAINT(TC_TEXT) VALUES('The first insert');
SELECT * FROM dbo.TBL_TEST_CONSTRAINT;
UPDATE dbo.TBL_TEST_CONSTRAINT
SET TC_TEXT = 'This is after update';
SELECT * FROM dbo.TBL_TEST_CONSTRAINT;
GO
CREATE TRIGGER dbo.TRG_TEST_CONSTRAINT_UPDATE_DATE
ON dbo.TBL_TEST_CONSTRAINT
AFTER UPDATE
AS
BEGIN
UPDATE T
SET T.TC_DATE_DEFAULT = GETDATE()
FROM dbo.TBL_TEST_CONSTRAINT T
WHERE T.TC_ID IN (SELECT I.TC_ID FROM inserted I);
END
GO
UPDATE dbo.TBL_TEST_CONSTRAINT
SET TC_TEXT = 'This is after the second update';
SELECT * FROM dbo.TBL_TEST_CONSTRAINT;
DROP TABLE dbo.TBL_TEST_CONSTRAINT;
Results
TC_ID TC_DATE_DEFAULT TC_TEXT
----------- ----------------------- ---------------------------------
1 2014-08-31 16:42:38.900 The first insert
TC_ID TC_DATE_DEFAULT TC_TEXT
----------- ----------------------- ---------------------------------
1 2014-08-31 16:42:38.900 This is after update
TC_ID TC_DATE_DEFAULT TC_TEXT
----------- ----------------------- ---------------------------------
1 2014-08-31 16:42:38.990 This is after the second update
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply