-- create table product (aid int,name varchar(10),price numeric(18,2)) -- -- insert product select '1','aaa','18' union all -- select '2','bbb','25' union all -- select '3','ccc','36.9' -- -- create table pstore (aid int,name varchar(10),num varchar(10)) -- insert pstore select '1','aaa','999' union all -- select '2','bbb','19999' union all -- select '3','ccc','25862' select * from product select * from pstore-- create procedure pr_update @aid int,@name varchar(10) -- as -- begin -- update product set name=@name where aid=@aid -- -- update pstore set name=b.name from pstore a,product b where a.aid=b.aid and b.aid=@aid -- endexec pr_update '3','ccc01'
--触发器实现 if object_id('产品表','U') is not null drop table 产品表 go create table 产品表 ( 产品ID int, 产品名称 varchar(10), 产品价格 int ) go if object_id('库存表','U') is not null drop table 库存表 go create table 库存表 ( 产品ID int, 产品名称 varchar(10), 库存数量 int ) go --只实现了插入的触发,楼主可以根据需要实现update,delete的触发器 if object_id('tr_cp_insert','tr') is not null drop trigger tr_cp_insert go create trigger tr_cp_insert on 产品表 for insert as if exists(select 1 from 库存表 where 产品id in(select 产品id from inserted)) update 库存表 set 库存数量=库存数量+1 else insert into 库存表 select 产品ID,产品名称,1 from inserted go insert into 产品表 select 1,'矿泉水',10 select * from 库存表 /* 产品ID 产品名称 库存数量 ----------- ---------- ----------- 1 矿泉水 1(1 行受影响)*/
-- create table product (aid int,name varchar(10),price numeric(18,2))
--
-- insert product select '1','aaa','18' union all
-- select '2','bbb','25' union all
-- select '3','ccc','36.9'
--
-- create table pstore (aid int,name varchar(10),num varchar(10))
-- insert pstore select '1','aaa','999' union all
-- select '2','bbb','19999' union all
-- select '3','ccc','25862' select * from product
select * from pstore-- create procedure pr_update @aid int,@name varchar(10)
-- as
-- begin
-- update product set name=@name where aid=@aid
--
-- update pstore set name=b.name from pstore a,product b where a.aid=b.aid and b.aid=@aid
-- endexec pr_update '3','ccc01'
--触发器实现
if object_id('产品表','U') is not null
drop table 产品表
go
create table 产品表
(
产品ID int,
产品名称 varchar(10),
产品价格 int
)
go
if object_id('库存表','U') is not null
drop table 库存表
go
create table 库存表
(
产品ID int,
产品名称 varchar(10),
库存数量 int
)
go
--只实现了插入的触发,楼主可以根据需要实现update,delete的触发器
if object_id('tr_cp_insert','tr') is not null
drop trigger tr_cp_insert
go
create trigger tr_cp_insert on 产品表
for insert
as
if exists(select 1 from 库存表 where 产品id in(select 产品id from inserted))
update 库存表 set 库存数量=库存数量+1
else
insert into 库存表 select 产品ID,产品名称,1 from inserted
go
insert into 产品表 select 1,'矿泉水',10
select * from 库存表
/*
产品ID 产品名称 库存数量
----------- ---------- -----------
1 矿泉水 1(1 行受影响)*/