want help in sql triggers

Question

5.00/5 (1 vote)

See more:

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

Posted 7-Nov-11 18:59pm

anoop_vip

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Vivek Krishnamurthy · Answer 1 · 2011-11-07T19:14:00

Solution 1

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

Posted 7-Nov-11 19:14pm

Vivek Krishnamurthy

Amir Mahfoozi · Answer 2 · 2011-11-07T19:37:00

Solution 2

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.

Posted 7-Nov-11 19:37pm

Amir Mahfoozi

Updated 7-Nov-11 19:56pm

v2

Comments

Bala Selvanayagam 8-Nov-11 12:14pm

5ed

Bala Selvanayagam · Answer 3 · 2011-11-07T20:05:00

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

1. Create t1

SQL

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

SQL

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

SQL

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

4.Create stored procedure

SQL

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