Mysql触发器各种操作

--触发器
--create trigger 名字
--on 表名
--触发动作
--for/after insert/update/delete
--as
--t-sql
select * from shop_jb
--insert into shop_xs values(6,4000,1,GETDATE(),3)
-- update shop_jb set stock=stock-1 where id=6

--创建触发器
create trigger tri1
on shop5_xs
after insert
as
update shop3_jb set stock=stock-1 where id=6

---触发器更新完之后,要查看表
create trigger tri3
on shop3_jb
for update
as
select * from shop3_jb

update shop3_jb set spec='sp-Z99' where id=2

drop trigger tri1

---用存储过程来实现,更新完之后,要查看表
alter proc proc1
as
update shop3_jb set spec='sp-111' where id=2
select * from shop3_jb

exec proc1

---查看表的触发器
exec sp_helptrigger 表名

---查看表的内容
exec sp_helptext 触发器名

---删除触发器
drop trigger 触发器名

----------练习--------
create trigger tri1
on shop3_jb
after insert
as
select * from inserted
insert into shop3_jb values('洗衣机','aa',200,500,getdate())

select * from shop3_jb

exec sp_helptrigger shop3_jb
exec sp_helptext tri1

drop trigger tri1

create trigger tri2
on shop3_jb
after delete
as
select * from deleted
delete from shop3_jb where namel='笔记本'

select * from shop3_jb

---查看表的触发器
exec sp_helptrigger 表名

---查看表的内容
exec sp_helptext 触发器名

---删除触发器
drop trigger 触发器名

select * from shop5_xs
select * from shop3_jb