Difference between a trigger and a transaction I'm implementing movie rating service, so I'm considering about using triggers or transactions.

What I'm considering is inserting movie information into a movie_list and to insert the genre of that movie into movie_genre_list.

In this case, my thinking is to write a trigger to insert the genre when the movie information is inserted.

But, I don't know whether a trigger or a transaction is right for this case. What should I do?
Comparing TRIGGERs and TRANSACTIONs is like comparing apples and oranges! No, wait, it's not, because apples and oranges are similar - they are both fruit and can both be eaten. It's more like chalk and cheese (English expression)!  


Transactions are for grouping actions into one unit of work which either succeeds or fails in its entirety! 
Triggers are (normally) for performing (procdural/business logic) code when an event (normally an INSERT, UPDATE or DELETE on a particular table) occurs in the database.


TRANSACTIONs.

These units of work are treated in a coherent and reliable way independent of other transactions.

From Wikipedia:


  
  To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system
  failure, when  execution stops (completely or partially) and many
  operations upon a database  remain uncompleted, with unclear status.
  To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the programs'
  outcomes are possibly  erroneous.
  


Note the use of the word "isolation" - this is one of the criteria for the "ACID" test. ACID in this context corresponds to both a play on words in English and a set of properties of database transactions that are considered highly desirable - these are: Atomicicty, Consistency, Isolation and Durablility.

The classic example of this is in banking. Say you want to pay your credit card bill of €100 by transferring money from your current account. So, it's important for both you and the bank that this "transaction" should occur as one single consistent unit of work. Otherwise, either you (money leaves your current account but doesn't get to the CC account), or the bank (your CC get credited but your current account isn't debited) is out of pocket.

So, in SQL, what you would do is:

BEGIN TRANSACTION;
UPDATE current_account ca SET ca.balance = ca.balance - 100 WHERE ca.cust_name = 'Haram';
UPDATE      CC_account cc SET cc.balance = cc.balance + 100 WHERE cc.cust_name = 'Haram';
COMMIT;  -- definitively perform the work.


If any of the steps fail (e.g. the balance in the current_account is insufficient), all of the work of the transaction will be rolled back - thus ensuring consistency.

Final point - if only it were all that simple! :-) There are (at least) four complicating factors:

1) Different isolation levels. This is beyond the scope of this answer - check out the Wiki and use your search engine of choice - there's loads of material out there, and

2) Different implementations by vendors - you'll have to look at your own system(s)' documentation for this, and

3) There are such things as nested TRANSACTIONs and SAVEPOINTs - again, this is getting beyond the scope of this answer - check your documentation, and

4) Some database engines support Transactional DDL (Data Definition Language) and some (notably MySQL) don't.

TRIGGERs.

A database TRIGGER on the other hand is a different animal altogether! A TRIGGERis an action (coded by the developer) which occurs in response to events on a table (again, there are complications which I won't go into here - check the Wiki and/or use your search engine). In the vast majority of cases, the event in question is an INSERT, an UPDATE or a DELETE on a table.

A rather nice analogy can be found here. An action on a database is compared to a row of dominoes - you knock over the first one and rest come tumbling down in a chain reaction. This is similar to a TRIGGER which will cause further events on other tables and so on! 

It is best, however, not to take the analogy too far - you want to avoid doing complex multi-table chained updates in so far as is possible - KISS. You can see how circular references (TRIGGER on  A UPDATEs B which in turn UPDATEs A...) might be disastrous!

From here:

You can use triggers to perform the following actions, as well as others that are not found in this list:


  
  Create an audit trail of activity in the database. For example, you can track updates to the orders table by updating corroborating
  information to an audit table.
  Implement a business rule. For example, you can determine when an order exceeds a customer's credit limit and display a message to that
  effect.
  Derive additional data that is not available within a table or within the database. For example, when an update occurs to the
  quantity column of the items table, you can calculate the
  corresponding adjustment to the total_price column.
  


(The link also mentions using them to enforce DRI (Declarative Referential Integrity) - I disagree - see end of answer).

A typical (vendor syntax varies considerably here - check your system's documentation) TRIGGER skeleton (example taken from here) would be:

CREATE
  TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
  ON `database`.`table`
  FOR EACH ROW BEGIN
  -- trigger body
  -- this code is applied to every 
  -- inserted/updated/deleted row
END;


and the body might look something like this:

IF NEW.deleted THEN
  SET @changetype = 'DELETE';
ELSE
  SET @changetype = 'NEW';
END IF;
INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);


This trigger is INSERTING a record into a table called audit with the id from the audited table (called blog in this case) and also recording whether the action was a DELETEor an INSERT. You could also have TIMESTAMP logic in there as well to keep track of when the action took place. Furthermore, you could add in user_name logic to audit who INSERTed/DELETEd what and when. The possibilities are endless.

Summary.

So, to sum up, a TRANSACTION groups a set of statements into a logical unit and a TRIGGER permits the inclusion of logic beyond the scope of SQL (complex business rules) in the database itself. 

One thing I found tricky at the beginning was the idea that (if no TRANSACTION is specified), the server will set up an implicit transaction on your behalf and COMMIT after each statement.

For example, here is the default behaviour of the MySQL server:

mysql&gt; show variables like '%COMMIT%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| autocommit                              | ON    |


Issue the command 

mysql&gt; SET autocommit = OFF;
Query OK, 0 rows affected (0.00 sec)


Now, you can experiment with what happens if you issue several commands in a row and issue a COMMIT and also when you exit without COMMITting.

Better yet, you can experiment with isolation levels 

mysql&gt; show variables like '%ISOL%'; 
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+


and then open different terminal sessions and see what happens to record visibility under different conditions.

Very last word: It looks to me that you are on the beginning of your database journey. Even though I have used MySQL for examples here, I would strongly (strongly) urge you to use PostgreSQL whenever possible - it is a vastly superior database server to MySQL and much more standards compliant to boot! Try and use the command line client whenever possible - can be fiddly at the beginning, but much more powerful and worth it in the long run! 

Very very last word ( :-) ) - keep reading and get some good texts and study them! p.s. Congratulations on your first questions on dba.stackexchange!

Oooops - almost forgot to answer the question asked! 

You can either (if you have one and only one (main) genre per film do this:


create a genre table and have a FOREIGN KEY on your movie table pointing to the genre_name field (fiddle1),


Or (if you wish to have more than one genre per film - a far more interesting and realistic scenario) do this:


create a genre and movie table as above, but also have a movie_genre joining table (or Associative Entity) between the two (fiddle2).