sql server trigger 触发器

sql server trigger 触发器

 insert触发器，

--创建insert插入类型触发器
if (object_id('tgr_userinfo_insert', 'tr') is not null)
    drop trigger tgr_userinfo_insert
go
create trigger tgr_userinfo_insert
on [t_song].[dbo].[userinfo]
    for insert --插入触发
as
    --定义变量
    declare @id int, @name varchar(50), @age int;
    --在inserted表中查询已经插入记录信息
    select @id = id, @name = username,@age=age from inserted;
    insert into [t_song].[dbo].[userinfo_copy] values(@id,@name,@age);
    print '添加info成功！';
go


--插入数据
insert into [t_song].[dbo].[userinfo] values('name1',11);


--查询数据
select * from [t_song].[dbo].[userinfo];
select * from [t_song].[dbo].[userinfo_copy] order by id;

-----

demo2:

--创建insert插入类型触发器
if (object_id('tgr_SCION_TEST_insert', 'tr') is not null)
    drop trigger tgr_SCION_TEST_insert
go
create trigger tgr_SCION_TEST_insert
on [eGActiveDB].[dbo].[SCION_TEST]
    for insert --插入触发
as
    --定义变量
    declare @url varchar(256),@result_id int;
    --在inserted表中查询已经插入记录信息
    select @url = url from inserted;
    update [eGActiveDB].[dbo].[SCION_TEST]
            set result_id=(
                    select search_result_id 
                    From [eGActiveDB].[dbo].[egsl_adapter_search_results] a
                    where a.url=@url)
            where url=@url;
    
    print 'update[eGActiveDB].[dbo].[SCION_TEST] success';
go

--删除数据
truncate table [eGActiveDB].[dbo].[SCION_TEST]
--插入数据
insert into [eGActiveDB].[dbo].[SCION_TEST](url) values ('https://weibo.com/hpanca_221/status/2602777280')

--查询数据
select * from [eGActiveDB].[dbo].[SCION_TEST]
select * From [eGActiveDB].[dbo].[egsl_adapter_search_results]

---

--创建insert插入类型触发器
if (object_id('tgr_egsl_adapter_search_results_insert_test', 'tr') is not null)
    drop trigger tgr_egsl_adapter_search_results_insert_test
go
create trigger tgr_egsl_adapter_search_results_insert_test
on [eGActiveDB].[dbo].[egsl_adapter_search_results]
    for insert --插入触发
as
    --定义变量
    declare @url varchar(256),@result_id int;
    --在inserted表中查询已经插入记录信息
    select @url = url,@result_id = search_result_id from inserted;
    update [eGActiveDB].[dbo].[SCION_RESULT]
            set result_id=@result_id
            where url=@url;
    print 'update[eGActiveDB].[dbo].[SCION_RESULT] success';
go

---

sql select 出的字段，怎么让它多出一列

select t.a, t.b from table t

select t.a, t.b,'c'as c  from table t

---