主流数据库的更新时间戳

 SQL Server:

-- 创建表 
DROP TABLE [t_test_timestamp];
GO

CREATE TABLE [dbo].[t_test_timestamp](
    [id] [INT] NOT NULL,
    [name] [VARCHAR](255) NULL,
    [updated_at] [DATETIME],
 CONSTRAINT [PK__t_test_t__3213E83F108B795B] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TRIGGER [dbo].[Tr_t_test_timestamp_insert] ON [dbo].[t_test_timestamp] after insert
    AS  
 BEGIN
 Set Nocount On; /*屏蔽触发器里Insert语句执行完后返回的所影响的行数的消息*/    
 DECLARE mycursor CURSOR local  FOR SELECT id FROM inserted  
    DECLARE @id INT      
    OPEN mycursor  
    FETCH NEXT FROM mycursor INTO @id
    WHILE @@fetch_status = 0  
    BEGIN          
        UPDATE dbo.t_test_timestamp SET updated_at=GETDATE() WHERE id=@id 
        FETCH NEXT FROM mycursor INTO @id
    END  
    CLOSE mycursor  
    DEALLOCATE mycursor  
 END

GO



CREATE TRIGGER [dbo].[Tr_t_test_timestamp_update] ON [dbo].[t_test_timestamp] after update  
    AS  
 BEGIN
 Set Nocount On; /*屏蔽触发器里Insert语句执行完后返回的所影响的行数的消息*/    
 DECLARE mycursor CURSOR local  FOR SELECT id FROM inserted  
    DECLARE @id INT      
    OPEN mycursor  
    FETCH NEXT FROM mycursor INTO @id
    WHILE @@fetch_status = 0  
    BEGIN   
        IF NOT UPDATE(updated_at)
        BEGIN      
            UPDATE dbo.t_test_timestamp SET updated_at=GETDATE() WHERE id=@id 
        END        
        FETCH NEXT FROM mycursor INTO @id
    END  
    CLOSE mycursor  
    DEALLOCATE mycursor  
 END
GO


-- 测试插入数据
insert into t_test_timestamp(id,name) values(1,'北京') ;
insert into t_test_timestamp(id,name) values(2,'上海') ;


SELECT * FROM dbo.t_test_timestamp;

-- 测试更新数据
update t_test_timestamp set name='北京2' where id=1;
SELECT * FROM dbo.t_test_timestamp;    

SqlServer版本的只读用户增加办法：
https://blog.csdn.net/enweitech/article/details/47393657 

 

Oracle版本：

 1)创建表空间

/*分为四步 */
/*第1步：创建临时表空间  */
create temporary tablespace yuhang_temp
tempfile 'E:\oracledata\yuhang_temp.dbf'
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 
 
/*第2步：创建数据表空间  */
create tablespace yuhang_data 
logging 
datafile 'E:\oracledata\yuhang_data.dbf'
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 
 
/*第3步：创建用户并指定表空间  */
create user yuhang identified by yuhang 
default tablespace yuhang_data 
temporary tablespace yuhang_temp; 
 
/*第4步：给用户授予权限  */
grant connect,resource,dba to yuhang;

 

 2) 创建表与触发器

-- Create table
create table T_TEST_TIMESTAMP
(
  id         NUMBER not null,
  name       VARCHAR2(255),
  updated_at DATE
)
tablespace YUHANG_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_TEST_TIMESTAMP
  add constraint ID primary key (ID)
  using index 
  tablespace YUHANG_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );



create or replace trigger sys.Tr_t_test_timestamp_modify
  before insert or update
  on YUHANG.T_TEST_TIMESTAMP 
  for each row
declare
  -- local variables here
begin
   IF INSERTING THEN
        :NEW.updated_at := SYSDATE;
    ELSIF UPDATING then
        :NEW.updated_at := SYSDATE;
    END IF;    
    
end Tr_t_test_timestamp_modify;

 

 3)测试

-- 测试插入数据
insert into T_TEST_TIMESTAMP(id,name) values(1,'北京') ;
insert into T_TEST_TIMESTAMP(id,name) values(2,'上海') ;
commit;

SELECT * FROM t_test_timestamp;

-- 测试更新数据
update t_test_timestamp set name='北京2' where id=1;
commit;

SELECT * FROM t_test_timestamp;    

Oracle只读用户的创建办法：https://www.cnblogs.com/xinxin1994/p/6290314.html 

 

 

MYSQL数据库　　

CREATE TABLE `trg_t_test_timestamp_insert`  
(
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `update_time` timestamp(0) NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


-- 也可以采取如下的触发器方式

-- 创建时间戳触发器INSERT
create trigger `trg_t_test_timestamp_insert` before insert 
on `t_test_timestamp` 
for each row set new.update_time = current_time;

-- 创建时间戳触发器UPDATE
create trigger `trg_t_test_timestamp_update` before update 
on `t_test_timestamp` 
for each row set new.update_time = current_time;


insert into `t_test_timestamp`(id,`name`) values(1,'北京') ;
insert into `t_test_timestamp`(id,`name`) values(2,'上海') ;

select * from `t_test_timestamp`;

1    北京    2018-06-28 15:35:42
2    上海    2018-06-28 15:35:42


update `t_test_timestamp` set `name`='北京2' where id=1;

select * from `t_test_timestamp`;

 Mysql只读用户的创建办法：https://www.cnblogs.com/sybblogs/p/8796275.html

 

关于数据的删除是物理性删除，而且逻辑删除update b_deleted=1 这样的已经在运行中的设计，我们的办法是增加deleted触发器，生成删除日志表，在上报的前置机中，读取删除日志表，并根据业务删除大数据中心中的数据信息（这时是逻辑删除，即b_deleted=1）。