在sql触发器中需要帮助 [英] want help in sql triggers

问题描述

我在数据库中有3个表t1，t2，t3. t1包含字段(tid，taddres，tdate)，表t2具有字段(tiid，tcontcano).表t3具有两个字段(toneid，twoeid).

表3分别根据tid(表t1)和tiid(表t2)的两个字段(toneid，twoeid)都是伪造密钥

现在，我想在表t1和t2上创建一个触发器，以便每当分别在表t1和t2中插入，删除或更新任何数据时，数据就会插入到表t3中.

第二个问题是如何通过触发器获取在表t3中插入的tid(t1)和(tiid)值

是否可以创建一个触发器，该触发器在将数据插入两个表中时起作用

解决方案

我认为您应该使用事务并在事务中的Table3中插入一行.

伪代码
开始交易

插入table1
取得t1id

插入table2
取得t2id

用t1id和t2id插入table3

commit

我认为触发器不会解决您的问题.
无论操作是否在事务中，都应将其捆绑到StoredProcedure 中.
只需将参数传递给StoredProcedure ，然后对这三个表进行任何操作即可.

正如Amir所说的那样，您必须使用存储过程，这里是示例实现. />
1.创建t1

 创建  TABLE  [dbo].[t1](
    [tid] [ int ]  IDENTITY ( 1 ， 1 )不  NULL ，
    [taddress] [ varchar ](max) NULL ，
    [tdate] [日期]  NULL ，
  CONSTRAINT  [PK_t1]  PRIMARY   KEY  集群
(
    [tid]  ASC 
)与(PAD_INDEX = 关闭，STATISTICS_NORECOMPUTE = 关闭，IGNORE_DUP_KEY = 关闭，ALLOW_ROW_LOCKS = 打开，ALLOW_PAGE_LOCKS =  ON ) ON  [ PRIMARY ]
)打开 [ PRIMARY ] 

2.创建t2

 创建  TABLE  [dbo].[t2](
    [tiid] [ int ]  IDENTITY ( 1 ， 1 )不  NULL ，
    [tcontactno] [ varchar ]( 50 )> NULL ，
  CONSTRAINT  [PK_t2]  PRIMARY   KEY  集群
(
    [tiid]  ASC 
)与(PAD_INDEX = 关闭，STATISTICS_NORECOMPUTE = 关闭，IGNORE_DUP_KEY = 关闭，ALLOW_ROW_LOCKS = 打开，ALLOW_PAGE_LOCKS =  ON ) ON  [ PRIMARY ]
)打开 [ PRIMARY ] 

3.创建t3

 创建  TABLE  [dbo].[t3](
    [toneid] [ int ]  NULL ，
    [twoeid] [ int ]  NULL 
)打开 [ PRIMARY ] 

4.创建存储过程

 创建 过程 spInsertContact
     @地址  varchar (max)， @ contactno   varchar (max)， @ tdate   date 
 AS 
开始
     SET  没有计数 打开；
    声明  @ toneId   int ， @ twoeid   int 


    插入 插入 t1(taddress，tdate)值( @地址， @ tdate )
    设置 @ toneId = SCOPE_IDENTITY()


    插入 插入 t2(tcontactno)值(  @ contactno )；
    设置 @ twoeid = SCOPE_IDENTITY()

    插入 插入 t3(toneId，twoeid)值( @ toneId ， @ twoeid )

 END  

5.您可以示例执行存储过程

spInsertContact ''sample address1'',''sample contact 1'',''13 jun 1990''

希望这会有所帮助，我已经在SQL 2008中进行了测试，并且可以正常工作

hi i have 3 tables t1,t2,t3 in database. t1 contains fields(tid,taddres,tdate )and table t2 has fields(tiid,tcontcano).table t3 has two fields(toneid,twoeid).

table 3 both feilds(toneid,twoeid) are forging key depending upon tid(table t1) and tiid(table t2) respectively

now i want to create a trigger on table t1 and t2 such that whenever any data is inserted or deleted or updated in table t1 and t2 respectively then data gets inserted in table t3 .

second problem is how to get tid(t1) and (tiid) values inserted in table t3 through triggers

is it possible to create a single trigger which acts when a data get inserted in two tables

解决方案

I think you should use transaction and insert a row in Table3 in a transaction.

pseudocode
begin transaction

insert into table1
get t1id

insert into table2
get t2id

insert into table3 with t1id and t2id

commit

I think your problem will not be solved by triggers.
You should bundle your operations into a StoredProcedure whether they are in a transaction or not.
Just pass the parameters to the StoredProcedure and do whatever you want with those three tables.

As correctly said by Amir, you have to use stored procedure and here goes the sample implementation

1. Create t1

CREATE TABLE [dbo].[t1](
    [tid] [int] IDENTITY(1,1) NOT NULL,
    [taddress] [varchar](max) NULL,
    [tdate] [date] NULL,
 CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED
(
    [tid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

2.Create t2

CREATE TABLE [dbo].[t2](
    [tiid] [int] IDENTITY(1,1) NOT NULL,
    [tcontactno] [varchar](50) NULL,
 CONSTRAINT [PK_t2] PRIMARY KEY CLUSTERED
(
    [tiid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

3.Create t3

CREATE TABLE [dbo].[t3](
    [toneid] [int] NULL,
    [twoeid] [int] NULL
) ON [PRIMARY]

4.Create stored procedure

Create PROCEDURE spInsertContact
    @address varchar(max),@contactno varchar(max),@tdate date
AS
BEGIN
    SET NOCOUNT ON;
    Declare @toneId int,@twoeid int


    insert into t1(taddress,tdate) values (@address,@tdate)
    set @toneId=SCOPE_IDENTITY()


    insert into t2(tcontactno) values (@contactno);
    set @twoeid=SCOPE_IDENTITY()

    insert into t3(toneId,twoeid) values (@toneId,@twoeid)

END

5. You can sample execute the stored procedure

spInsertContact ''sample address1'',''sample contact 1'',''13 jun 1990''

hope this helps, i have tested in SQL 2008 and it works

这篇关于在sql触发器中需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！