用于添加和删除的 DB2 iSeries 触发器修改时间戳和用户字段

发布于 2024-11-04 00:57:39 字数 348 浏览 4 评论 0原文

也许添加/修改日期不需要触发器，也许有适当的函数来设置它们的值，在任何情况下：

我的问题是以下字段，

created (timestamp)
updated (timestamp)
createdBy (string, to hold the created by user name)
updatedBy (string, to hold the updated by user name)

如何更改表，以便在创建和更新这些字段时保留适当的值价值观？

编辑：我现在只需要知道如何在每次访问记录时设置updatedBy和updated时间戳字段。

原文

Perhaps triggers are not needed for added/modifed dates, maybe there are appropriate functions to set their values, in any case:

My question is with the following fields,

created (timestamp)
updated (timestamp)
createdBy (string, to hold the created by user name)
updatedBy (string, to hold the updated by user name)

how do I alter the table such that on creation and update these fields hold the appropriate values?

Edit: I now just need to know how to set the updatedBy and updated timestamp fields each time the record is accessed.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

烟织青萝梦 2024-11-11 00:57:39

创建下表作为参考：

create table test(                    
  id integer generated always as identity,    
  content char(60),                           
  createdBy char(30) default user,            
  created timestamp default current timestamp,
  updatedBy char(30),                         
  updated timestamp default null,             
  primary key(id)                             
)

该表有一个自动递增的主键（id），一个在插入时设置的createdBy字段，一个在插入时设置的创建时间戳，现在我们只需要触发器来使最后两个按预期工作（有一个新功能可以在不使用触发器的情况下设置更新更新，但该功能似乎不允许空值来显示记录从未更新过，因此这对我不起作用）。

insert into test (content) VALUES ('first thing'),
  ('second thing')

要查看已设置created和createdBy的默认值：

select * from test

要添加更新触发器：

CREATE TRIGGER mytrigger                   
  NO CASCADE BEFORE UPDATE ON test 
  REFERENCING NEW AS post                  
FOR EACH ROW MODE DB2ROW                   
SET                                        
 post.updated = CURRENT TIMESTAMP,         
 post.updatedBy = USER

要查看上述内容是否有效，让我们更新“content”中的值：

update co05arh/test
  set content = 'first thing updated'
  where id = 1

要查看新的默认值，

select * from co05arh/test

我们应该看到类似的内容

ID  CONTENT                 CREATEDBY   CREATED                     UPDATEDBY   UPDATED
1   first thing updated     KEN         2011-04-29 16:16:17.942429  KEN         2011-04-29 16:16:28.649543
2   second thing            KEN         2011-04-29 16:16:18.01629   <null>      <null>

Create the following table for a reference:

create table test(                    
  id integer generated always as identity,    
  content char(60),                           
  createdBy char(30) default user,            
  created timestamp default current timestamp,
  updatedBy char(30),                         
  updated timestamp default null,             
  primary key(id)                             
)

This table has an auto incrementing primary key (id), a createdBy field which is set on insert, a created timestamp which is set on insert now we just need triggers to make the last two work as expected (there is a new feature to set updated on update without the use of triggers but the feature does not seem to allow a null value to show the record has never been updated so that does not work for me).

insert into test (content) VALUES ('first thing'),
  ('second thing')

To see that the default values for created and createdBy have been set:

select * from test

To add update triggers:

CREATE TRIGGER mytrigger                   
  NO CASCADE BEFORE UPDATE ON test 
  REFERENCING NEW AS post                  
FOR EACH ROW MODE DB2ROW                   
SET                                        
 post.updated = CURRENT TIMESTAMP,         
 post.updatedBy = USER

To see if the above is working, lets update the values in "content":

update co05arh/test
  set content = 'first thing updated'
  where id = 1

To see the new default values

select * from co05arh/test

We should then see something like

ID  CONTENT                 CREATEDBY   CREATED                     UPDATEDBY   UPDATED
1   first thing updated     KEN         2011-04-29 16:16:17.942429  KEN         2011-04-29 16:16:28.649543
2   second thing            KEN         2011-04-29 16:16:18.01629   <null>      <null>

回复收藏 0 原文

~没有更多了~