|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Trigger help followup.post was this... << CREATE TABLE [dbo].[Employee] ( [EmployeeName] [varchar] (50), [ActiveFlag] [smallint], [ActiveTS] [datetime] ) The front end is a vb app. When a column gets updated to a non-zero, the client wants to capture the date and time of the change. If the value is a zero, or gets changed back to zero, then the datetime field needs to be reset to null. >> The answer I got is listed below, which worked great !! Now, is there code I can put in to check if the value in the activeflag column is already 1, not update the timestamp value. Thanks again for your help. CREATE TRIGGER tr_Employee_U on Employee FOR UPDATE AS set nocount on IF UPDATE(ActiveFlag) BEGIN update employee set ActiveTS = case when coalesce((select ActiveFlag from inserted as i where i.EmployeeName = Employee.EmployeeName), 0) != 0 then getdate() else null end where exists(select * from inserted as i where i.EmployeeName = Employee.EmployeeName) END go Does this help?
CREATE TRIGGER tr_Employee_U on Employee FOR UPDATE AS set nocount on IF UPDATE(ActiveFlag) BEGIN if (select ActiveFlag from inserted) <> 1 update employee set ActiveTS = case when coalesce((select ActiveFlag from inserted as i where i.EmployeeName = Employee.EmployeeName), 0) != 0 then getdate() else null end where exists(select * from inserted as i where i.EmployeeName = Employee.EmployeeName) END go "Jack" <j***@jack.net> wrote in message news:XRZne.27359$iU.23092@lakeread05...Show quoteHide quote > Thanks to Alejondro for posting a solution to my original post. My original > post was this... > << > CREATE TABLE [dbo].[Employee] ( > [EmployeeName] [varchar] (50), > [ActiveFlag] [smallint], > [ActiveTS] [datetime] > ) > > The front end is a vb app. When a column gets updated to a non-zero, the > client wants to capture the date and time of the change. If the value is a > zero, or gets changed back to zero, then the datetime field needs to be > reset to null. > >> > > The answer I got is listed below, which worked great !! Now, is there code > I can put in to check if the value in the activeflag column is already 1, > not update the timestamp value. Thanks again for your help. > > CREATE TRIGGER tr_Employee_U on Employee > FOR UPDATE > AS > set nocount on > > IF UPDATE(ActiveFlag) > BEGIN > update employee > set ActiveTS = case when coalesce((select ActiveFlag from inserted as i > where i.EmployeeName = Employee.EmployeeName), 0) != 0 then getdate() > else null end > where exists(select * from inserted as i where i.EmployeeName = > Employee.EmployeeName) > END > go > > > This will not cope with multiple rows being updated!
John Show quoteHide quote "Armando Prato" <apr***@REMOVEMEkronos.com> wrote in message news:uhvR83EaFHA.3840@tk2msftngp13.phx.gbl... > Does this help? > > CREATE TRIGGER tr_Employee_U on Employee > FOR UPDATE > AS > set nocount on > > IF UPDATE(ActiveFlag) > BEGIN > if (select ActiveFlag from inserted) <> 1 > update employee > set ActiveTS = case when coalesce((select ActiveFlag from inserted as i > where i.EmployeeName = Employee.EmployeeName), 0) != 0 then getdate() > else null end > where exists(select * from inserted as i where i.EmployeeName = > Employee.EmployeeName) > END > go > > > "Jack" <j***@jack.net> wrote in message > news:XRZne.27359$iU.23092@lakeread05... >> Thanks to Alejondro for posting a solution to my original post. My > original >> post was this... >> << >> CREATE TABLE [dbo].[Employee] ( >> [EmployeeName] [varchar] (50), >> [ActiveFlag] [smallint], >> [ActiveTS] [datetime] >> ) >> >> The front end is a vb app. When a column gets updated to a non-zero, the >> client wants to capture the date and time of the change. If the value is > a >> zero, or gets changed back to zero, then the datetime field needs to be >> reset to null. >> >> >> >> The answer I got is listed below, which worked great !! Now, is there > code >> I can put in to check if the value in the activeflag column is already 1, >> not update the timestamp value. Thanks again for your help. >> >> CREATE TRIGGER tr_Employee_U on Employee >> FOR UPDATE >> AS >> set nocount on >> >> IF UPDATE(ActiveFlag) >> BEGIN >> update employee >> set ActiveTS = case when coalesce((select ActiveFlag from inserted as i >> where i.EmployeeName = Employee.EmployeeName), 0) != 0 then getdate() >> else null end >> where exists(select * from inserted as i where i.EmployeeName = >> Employee.EmployeeName) >> END >> go >> >> >> > > Hmmmm.... yes, you're correct. I took a stab.
Show quoteHide quote "John Bell" <jbellnewspo***@hotmail.com> wrote in message news:e3$ltiFaFHA.3712@TK2MSFTNGP09.phx.gbl... > This will not cope with multiple rows being updated! > > John > "Armando Prato" <apr***@REMOVEMEkronos.com> wrote in message > news:uhvR83EaFHA.3840@tk2msftngp13.phx.gbl... > > Does this help? > > > > CREATE TRIGGER tr_Employee_U on Employee > > FOR UPDATE > > AS > > set nocount on > > > > IF UPDATE(ActiveFlag) > > BEGIN > > if (select ActiveFlag from inserted) <> 1 > > update employee > > set ActiveTS = case when coalesce((select ActiveFlag from inserted as i > > where i.EmployeeName = Employee.EmployeeName), 0) != 0 then getdate() > > else null end > > where exists(select * from inserted as i where i.EmployeeName = > > Employee.EmployeeName) > > END > > go > > > > > > "Jack" <j***@jack.net> wrote in message > > news:XRZne.27359$iU.23092@lakeread05... > >> Thanks to Alejondro for posting a solution to my original post. My > > original > >> post was this... > >> << > >> CREATE TABLE [dbo].[Employee] ( > >> [EmployeeName] [varchar] (50), > >> [ActiveFlag] [smallint], > >> [ActiveTS] [datetime] > >> ) > >> > >> The front end is a vb app. When a column gets updated to a non-zero, the > >> client wants to capture the date and time of the change. If the value is > > a > >> zero, or gets changed back to zero, then the datetime field needs to be > >> reset to null. > >> >> > >> > >> The answer I got is listed below, which worked great !! Now, is there > > code > >> I can put in to check if the value in the activeflag column is already 1, > >> not update the timestamp value. Thanks again for your help. > >> > >> CREATE TRIGGER tr_Employee_U on Employee > >> FOR UPDATE > >> AS > >> set nocount on > >> > >> IF UPDATE(ActiveFlag) > >> BEGIN > >> update employee > >> set ActiveTS = case when coalesce((select ActiveFlag from inserted as i > >> where i.EmployeeName = Employee.EmployeeName), 0) != 0 then getdate() > >> else null end > >> where exists(select * from inserted as i where i.EmployeeName = > >> Employee.EmployeeName) > >> END > >> go > >> > >> > >> > > > > > > Hi
Try something like (untested): CREATE TRIGGER tr_Employee_U on Employee INSTEAD OF UPDATE AS IF @@ROWCOUNT = 0 RETURN SET NOCOUNT ON UPDATE E SET ActiveTS = CASE WHEN COALESCE(i.ACTIVEFLAG,0) != 0 AND E.ActiveFlag != 1 THEN GETDATE() ELSE E.ActiveTS END, ActiveFlag = i.ActiveFlag, ..... FROM Employee E JOIN INSERTED I ON i.EmployeeName = E.EmployeeName John > where i.EmployeeName = Employee.EmployeeName), 0) != 0 then getdate() news:XRZne.27359$iU.23092@lakeread05...> else null end > where exists(select * from inserted as i where ) > END > go "Jack" <j***@jack.net> wrote in message Show quoteHide quote > Thanks to Alejondro for posting a solution to my original post. My > original post was this... > << > CREATE TABLE [dbo].[Employee] ( > [EmployeeName] [varchar] (50), > [ActiveFlag] [smallint], > [ActiveTS] [datetime] > ) > > The front end is a vb app. When a column gets updated to a non-zero, the > client wants to capture the date and time of the change. If the value is > a > zero, or gets changed back to zero, then the datetime field needs to be > reset to null. >>> > > The answer I got is listed below, which worked great !! Now, is there > code I can put in to check if the value in the activeflag column is > already 1, not update the timestamp value. Thanks again for your help. > > CREATE TRIGGER tr_Employee_U on Employee > FOR UPDATE > AS > set nocount on > > IF UPDATE(ActiveFlag) > BEGIN > update employee > set ActiveTS = case when coalesce((select ActiveFlag from inserted as i > where i.EmployeeName = Employee.EmployeeName), 0) != 0 then getdate() > else null end > where exists(select * from inserted as i where i.EmployeeName = > Employee.EmployeeName) > END > go > > > you guys are awesome. Since it is a vb front-end, only one row is getting
updated at a time. Thank you for all your help. Show quoteHide quote "John Bell" <jbellnewspo***@hotmail.com> wrote in message news:OX3U3nFaFHA.464@TK2MSFTNGP15.phx.gbl... > Hi > > Try something like (untested): > > CREATE TRIGGER tr_Employee_U on Employee > INSTEAD OF UPDATE > AS > IF @@ROWCOUNT = 0 RETURN > SET NOCOUNT ON > > UPDATE E > SET ActiveTS = CASE WHEN COALESCE(i.ACTIVEFLAG,0) != 0 AND E.ActiveFlag != > 1 THEN GETDATE() > ELSE E.ActiveTS END, > ActiveFlag = i.ActiveFlag, > .... > FROM Employee E > JOIN INSERTED I ON i.EmployeeName = E.EmployeeName > > John > >> where i.EmployeeName = Employee.EmployeeName), 0) != 0 then getdate() >> else null end >> where exists(select * from inserted as i where ) >> END >> go > > "Jack" <j***@jack.net> wrote in message > news:XRZne.27359$iU.23092@lakeread05... >> Thanks to Alejondro for posting a solution to my original post. My >> original post was this... >> << >> CREATE TABLE [dbo].[Employee] ( >> [EmployeeName] [varchar] (50), >> [ActiveFlag] [smallint], >> [ActiveTS] [datetime] >> ) >> >> The front end is a vb app. When a column gets updated to a non-zero, the >> client wants to capture the date and time of the change. If the value is >> a >> zero, or gets changed back to zero, then the datetime field needs to be >> reset to null. >>>> >> >> The answer I got is listed below, which worked great !! Now, is there >> code I can put in to check if the value in the activeflag column is >> already 1, not update the timestamp value. Thanks again for your help. >> >> CREATE TRIGGER tr_Employee_U on Employee >> FOR UPDATE >> AS >> set nocount on >> >> IF UPDATE(ActiveFlag) >> BEGIN >> update employee >> set ActiveTS = case when coalesce((select ActiveFlag from inserted as i >> where i.EmployeeName = Employee.EmployeeName), 0) != 0 then getdate() >> else null end >> where exists(select * from inserted as i where i.EmployeeName = >> Employee.EmployeeName) >> END >> go >> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics