SQL trigger, insert into statement

Question

I have two sql tables called scan_sc and rescan_rsc. The scan table looks like this:

CREATE TABLE scan_sc 
( 
   id_sc int(4), 
   Type_sc varchar(255), 
   ReScan_sc varchar(255), 
   PRIMARY KEY  (id_sc)
)

When a scan a document I insert a row into the scan table. If the result of this scanning is poor I have to do a rescan, and therefore I have a rescan table.

CREATE TABLE rescan_rsc 
(
   id_rsc int(4), 
   Scan_rsc varchar(255), 
   PRIMARY KEY  (id_rsc)
)

The problem is, I want to have a trigger that will fill in the column ReScannet_sc with an "x", in the scan_sc table, so I can see that there has been some problems here.

The trigger has to do it where the id from the rescan table is the same as in the scan table.

Hope you all understand my question.

Thanks in advance.

WHAT database and which version?? SQL is just the Structured Query Language - a language used by many database systems - SQL is NOT a database product... stuff like this is very often vendor-specific - so we really need to know what database system you're using.... — marc_s
– marc_s, Commented Sep 29, 2011 at 7:01
Neiter your scan_sc nor your rescan_rsc tables have a column called id_sd yet that's the primary key for both?? — marc_s
– marc_s, Commented Sep 29, 2011 at 7:03

Christian Specht · Accepted Answer · 2011-09-29 07:28:31Z

1

Do you really need the ReScan_sc column and the trigger?

With a simple JOIN, you can find out the records in your scan_sc table that have been re-scanned, without using the ReScan_sc column at all.
There are several possibilities:

Show all scans, with an additional column with the Rescan ID, if any:

SELECT scan_sc.*, rescan_sc.id_rsc
FROM scan_sc
LEFT JOIN rescan_sc ON scan_sc.id_sc = rescan_sc.id_rsc

Show only the scans which have been re-scanned:

SELECT scan_sc.*
FROM scan_sc
INNER JOIN rescan_sc ON scan_sc.id_sc = rescan_sc.id_rsc

(I assume that id_sc and id_rsc are the primary keys and that PRIMARY KEY (id_sd) is a typo, like marc_s pointed out in his comment)

answered Sep 29, 2011 at 7:28

Christian Specht

36.6k16 gold badges132 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thomas Over a year ago

Then i dont have a tabel, where the users can type in the rescanned documents, if i understand your solution correct.

Christian Specht Over a year ago

You would still have both tables. The rescan_sc table would remain as it is, only the Rescan_sc column in the scan_sc table would be gone (and you wouldn't need the trigger at all).

Christian Specht Over a year ago

You don't have to do it this way, this is just an alternative suggestion. You can still do it with a trigger if you want (but then I can't help you, because I don't know how to write triggers in MySQL).

Thomas Over a year ago

I think i will use your answer, its much easier for me to understand. And i dont have much experience with triggers.

Collectives™ on Stack Overflow

SQL trigger, insert into statement

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related