table1中插入这样一行
col1 col2
ip - text 2009-07-22 09:15:45.993
的时候,会触发insert table2这样一行
col3 col4 col5 col6
ip text 2009-07-22 09:15:45
请帮忙啦,谢谢!
col1 col2
ip - text 2009-07-22 09:15:45.993
的时候,会触发insert table2这样一行
col3 col4 col5 col6
ip text 2009-07-22 09:15:45
请帮忙啦,谢谢!
你把上面的分解一下就可以插到B表了啊
on table1
for insert
as
insert table1
select
left(col1,charindex('-',col1)-1),
right(col1,len(col1)-charindex('-',col1)),
convert(varchar(10),col2,108)
from
inserted
for insert
as
begin
insert table2(col3,col4,col5,col6)
select left(i.col1,charindex('-',i.col1)-1),
stuff(i.col1,1,charindex('-',i.col1)),
convert(varchar(10),i.col2,120),
stuff(convert(varchar(19),i.col2,120),1,11,'')
from inserted i
end
for insert
as
begin
insert table2(col3,col4,col5,col6)
select left(i.col1,charindex('-',i.col1)-1),
stuff(i.col1,1,charindex('-',i.col1),''),
convert(varchar(10),i.col2,120),
stuff(convert(varchar(19),i.col2,120),1,11,'')
from inserted i
end
修改下
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('table1') IS NOT NULL
DROP TABLE table1
GO
CREATE TABLE table1( col1 varchar(10),col2 datetime)
go
IF OBJECT_ID('table2') IS NOT NULL
DROP TABLE table2
GO
CREATE TABLE table2( col3 varchar(10),col4 varchar(10),col5 varchar(10),col6 varchar(10))
go
create trigger k on table1
for insert
as
begin
insert table2(col3,col4,col5,col6)
select left(i.col1,charindex('-',i.col1)-1),
stuff(i.col1,1,charindex('-',i.col1),''),
convert(varchar(10),i.col2,120),
stuff(convert(varchar(19),i.col2,120),1,11,'')
from inserted i
end
go
insert into table1 values( 'ip - text','2009-07-22 09:15:45.993')
go
select * from table2
/*
col3 col4 col5 col6
---------- ---------- ---------- ----------
ip text 2009-07-22 09:15:45
*/
测试
for insert
as
begin
insert table2(col3,col4,col5,col6)
select left(i.col1,charindex('-',i.col1)-1),
stuff(i.col1,1,charindex('-',i.col1),''),
convert(varchar(10),i.col2,120),
convert(varchar(10),getdate(),108)
from inserted i
end
table1中插入这样一行
col1 col2
ip - text 2009-07-22 09:15:45.993
的时候,会触发insert table2这样一行
col3 col4 col5 col6
ip text 2009-07-22 09:15:45
请帮忙啦,谢谢!
*/
IF OBJECT_ID('tb1') IS NOT NULL DROP TABLE tb1
CREATE TABLE tb1( col1 varchar(20),col2 datetime)IF OBJECT_ID('tb2') IS NOT NULL DROP TABLE tb2
CREATE TABLE tb2( col3 varchar(10),col4 varchar(10),col5 varchar(10),col6 varchar(10))create trigger trig_tb1 on TB1
for insert,update
as
begin
insert tb2(col3,col4,col5,col6)
select
substring(a.col1,1,charindex('-',a.col1+'-')-1), --以“-”为分隔符得到“-”左边字符
substring(a.col1,charindex('-',a.col1+'-')+1,len(a.col1)), -- --以“-”为分隔符得到“-”右边字符 暂时固定长度
CONVERT(varchar(10),getdate(),21), --得到****年**月**日格式
CONVERT(varchar(10),getdate(),108) --得到长日期格式中的时间部分
from inserted a
end
goinsert into tb1 values( 'ip - text','2009-07-22 09:15:45.993')
insert into tb1 values('TCP/IP-iptext',GETDATE())
go
/*
col3 col4 col5 col6
ip text 2009-08-31 10:19:53
TCP/IP iptext 2009-08-31 10:20:24
*/