I want to create trigger instead of using foreign key.

Question

0.00/5 (No votes)

See more:

Hi All,

I have 4 tables Table1, Table2, Table3, Table4. Whenever data is inserted in Table1, data is also insert in Table4. Similarly When data is inserted in Table2, and Table3 data is also insert in Table4. so we cannot create Foreign key on Table4.

so Please tell me, how to create trigger on Table1, Table2 and Table3 to preserve rows to delete it. if it is exists in Table4.

Thanks in advance.

What I have tried:

Create trigger trgDeleteDebitVoucher on TblDebitVoucher For Delete
As
IF EXISTS (SELECT * FROM TblStatement Where ProjectNo In (Select NoteNo From TblDebitVoucher))
BEGIN
RAISERROR ('This Voucher is used in Statement Table, So Cannot Delete It.', 16, 1);
ROLLBACK TRANSACTION;
END;

Posted 29-Nov-16 0:57am

kantagrawal

Updated 30-Nov-16 10:06am

Add a Solution

Comments

Tomas Takac 29-Nov-16 9:33am

I fail to see why you cannot create a foreign key, could you please elaborate?

Peter Leow 29-Nov-16 10:46am

Possible. For example, a foreign key value exists in table 1 cannot be inserted into table 4 if it does not also exist in the other tables.

2 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)

Peter Leow · Answer 1 · 2016-11-29T04:52:00

Solution 1

Take a step back and rethink your design. Any other way to get around this foreign key referencing multiple tables issue? As it is, there is also the danger of inserting duplicate primary key into multiple tables. I am proposing a separate table to hold the primary key and for the other four tables to link to it through foreign key. What do you think?

Posted 29-Nov-16 4:52am

Peter Leow

Comments

kantagrawal 30-Nov-16 0:09am

Dear Peter Leow,
we are not inserting the same primary key in Table4. the Primary key column values of Table1, Table2 and Table3 are different. and we are inserting these values in Table4. so they are different in column. but we cannot set this column of Table4 as foreign key, because we can set only one foreign key in a column of table to references the primary key. so i think we need to create trigger on Table1, Table2, Table3 for Delete. if that primary key value exists in Table4. if there is any other procedure, then please tell me.

Peter Leow 30-Nov-16 0:26am

Yes, the reason why you can't use foreign key is because that intended foreign key column in table 4 is referencing multiple tables 1, 2, 3, right? Even they are different in values in tables 1, 2, 3. I still stand by my suggestion. Do let me know if you figure out the trigger solution. I am always prepare to learn.

dbrenth · Answer 2 · 2016-11-30T10:06:00

In your trigger change this

(SELECT * FROM TblStatement Where ProjectNo In (Select NoteNo From TblDebitVoucher))

TO

(SELECT * FROM TblStatement Where ProjectNo In (Select NoteNo From deleted))

You could also switch to an INSTEAD OF trigger. Then you don't need to rollback the transaction, just don't run the delete at all.

Good Luck
Brent