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.


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.