INSERT trigger triggering UPDATE trigger | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

INSERT trigger triggering UPDATE trigger

Hi All,<br />I have a table with the following fields: <br />[TerminalId], [Variable], [Value], [CreatedDate] NULL, [UpdatedDate] NULL,<br />[UniqueId] IDENTITY (1, 1) NOT NULL<br /><br />The Primary Key is TerminalId and Variable. I want to Insert the CreatedDate when a record is inserted Update the UpdatedDate when the record is updated. I created the following 2 triggers:<br /><pre id="code"><font face="courier" size="2" id="code">CREATE TRIGGER trg_EnterCreatedDate ON [dbo].[tbl_TerminalDefaultValues] <br />FOR INSERT<br />AS<br /><br />UPDATE tbl_TerminalDefaultValues<br />SET CreatedDate = GETDATE()<br />FROM tbl_TerminalDefaultValues TDV INNER JOIN Inserted I ON TDV.TerminalId = i.TerminalId AND TDV.Variable = I.Variable</font id="code"></pre id="code"><br />AND<br /><pre id="code"><font face="courier" size="2" id="code">CREATE TRIGGER trg_EnterUpdatedDate ON [dbo].[tbl_TerminalDefaultValues] <br />FOR UPDATE<br />AS<br /><br />UPDATE tbl_TerminalDefaultValues<br />SET UpdatedDate = GETDATE()<br />FROM tbl_TerminalDefaultValues TDV INNER JOIN Inserted I ON TDV.TerminalId = i.TerminalId AND TDV.Variable = I.Variable</font id="code"></pre id="code"><br />The problem I have is that the Insert trigger is also triggering the Update trigger (at least that is what i think is happening!)<br /><br />Is there any way around this so the updated date will stay null until the record is updated?<br /><br />Thanks in advance,<br />Ben [<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />]<br /><br />
Create a single trigger that fires for both INSERT and UPDATE events, and let it set the CreatedDate only if it is null.
Define CreatedDate as not null with default getDate() instead of using trigger.
Thanks for the tips! I knew there had to be an easy solution!
Ben
]]>