如何为 Teradata 表中的更新时间戳创建触发器? [英] How to create a trigger for on update timestamp in Teradata table?

问题描述

我使用的是 Teradata 14.00

I am using Teradata 14.00

我在 Teradata 14.00 中有一个包含列(id、name、city、mytime)的表.

I have a table with columns (id, name,city,mytime) in Teradata 14.00.

如果我们更新名称，'mytime' 列中的时间戳应该会自动更新，而不必在 Update 语句中指定它.

If we update the name, the timestamp in 'mytime' column should be updated automatically, without having to specify it in the Update statement.

如何为此使用触发器.

任何建议.
提前致谢

Any suggestions.
Thanks in advance

推荐答案

这里有一篇很好的文章 http://teradatafaqs.blogspot.in/p/tutorial.html 用于研究触发器.

Here is a very good article http://teradatafaqs.blogspot.in/p/tutorial.html for studying triggers.

为了您的方便，这里是逐步创建触发器.

For your ease here is the step by step creating of the trigger.

示例表

Temp Table for your refernce :-
drop table DB_SOK.EMPLOYEE;
CREATE MULTISET TABLE DB_SOK.EMPLOYEE
( Employeeid INTEGER,
Employeename CHAR(30),
Salary INTEGER,
city varchar(100),
mytime timestamp(0));

insert into DB_SOK.EMPLOYEE values(1,'happy',100,'NYC',current_timestamp(0));

Select * from DB_SOK.EMPLOYEE;

Employeeid  Employeename    Salary  city    mytime
1               1           happy    100    NYC 11/7/2015 08:51:05

然后为每一行创建一个更新触发器.

Then Create a Trigger for update for each row.

Replace  TRIGGER DB_SOK.EmpUpdate
AFTER UPDATE OF (Employeename) ON DB_SOK.EMPLOYEE
REFERENCING OLD ROW as EMPLOYEE_old_row
 NEW row  as EMPLOYEE_new_row
 FOR EACH ROW
update DB_SOK.EMPLOYEE
set mytime=current_timestamp(0)

火灾更新:-

update DB_SOK.EMPLOYEE
set  Employeename ='Hello Happy'
where Employeeid=1;

再次选择Data查看数据是否更新；

Select Data again to see data is update or not;

Select * from DB_SOK.EMPLOYEE;

Employeeid  Employeename    Salary  city    mytime
1   1   Hello Happy                     100 NYC 11/7/2015 09:02:23

这篇关于如何为 Teradata 表中的更新时间戳创建触发器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！