Advanced Search

Update trigger for datetime modified field

/ Published in: SQL
Save to your folder(s)
This will create a table, create an update trigger to add current datetime to modify_date field, do 2 inserts, 1 update and display the results.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. /***CREATE TABLE
  2. SET ANSI_NULLS ON
  3. GO
  4.  
  5. SET QUOTED_IDENTIFIER ON
  6. GO
  7.  
  8. SET ANSI_PADDING ON
  9. GO
  10.  
  11. CREATE TABLE [dbo].[Users](
  12. 	[uid] [int] IDENTITY(1,1) NOT NULL,
  13. 	[fname] [varchar](50) NOT NULL,
  14. 	[lname] [varchar](50) NOT NULL,
  15. 	[title] [varchar](50) NULL,
  16. 	[manager] [varchar](2) NULL,
  17. 	[create_date] [datetime] NOT NULL,
  18. 	[modify_date] [datetime] NOT NULL
  19. ) ON [PRIMARY]
  20.  
  21. GO
  22.  
  23. SET ANSI_PADDING OFF
  24. GO
  25.  
  26. EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User a manager or not ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Users', @level2type=N'COLUMN',@level2name=N'manager'
  27. GO
  28.  
  29. ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_manager]  DEFAULT ((0)) FOR [manager]
  30. GO
  31.  
  32. ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_create_date]  DEFAULT (getdate()) FOR [create_date]
  33. GO
  34.  
  35. ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_modify_date]  DEFAULT (getdate()) FOR [modify_date]
  36. GO
  37.  
  38.  
  39. ***/
  40.  
  41.  
  42.  
  43. /****
  44. CREATE TRIGGER usermod
  45. on dbo.Users
  46. After UPDATE 
  47. AS BEGIN
  48.    Update dbo.Users
  49.    set modify_date = getdate()
  50.    from INSERTED i
  51.    Where i.uid = dbo.Users.uid
  52. END
  53. ***/
  54.  
  55. /***
  56. TEST INSERT
  57. insert into dbo.Users (fname,lname,title,manager) values ('bob','smith','Director','1')
  58. insert into dbo.Users (fname,lname,title,manager) values ('joe','jones','Manager','1')
  59. ***/
  60. /*** TEST UPDATE
  61. update dbo.Users set fname='joan' where uid = 2
  62. ***/
  63.  
  64. /****** Script for SelectTopNRows command from SSMS  
  65. SELECT TOP 10[uid]
  66.       ,[fname]
  67.       ,[lname]
  68.       ,[title]
  69.       ,[manager]
  70.       ,[create_date]
  71.       ,[modify_date]
  72.   FROM [dbo].[Users]
  73. ******/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.