How Do I Change The Value In Table Using Sql Trigger?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

I am trying to write SQL trigger to change the rental_rate of every new film inserted into the database on the basis of the following price chart.

special feature 'trailers' 10c increase special feature 'commentaries' 50c increase special feature 'Deleted Scenes' 20c increase special feature 'Behind the Scenes' 20c decrease

This is what I have so far:

CREATE OR REPLACE TRIGGER CHArraa
AFTER
INSERT ON film
DECLARE special_features varchar2(50);
rental_rate number;
IF (special_features = 'Trailers') THEN
           rental_rate := rental_rate + 0.10;
IF (special_features = 'Commentaries') THEN
           rental_rate:= rental_rate+ 0.50;
IF (special_features = 'Deleted Scenes') THEN
           rental_rate := rental_rate + 0.20;
IF (special_features = 'Behind the Scenes') THEN
          rental_rate := rental_rate - 0.20;
END IF;
END IF;
END IF;
END IF;
END;

Can I get some get help to fix and make this work

Posted 23-May-14 20:18pm

Pranav-BiTwiser

Add a Solution

1 solution

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)

RDBurmon · Answer 1 · 2014-05-23T20:52:00

Create INSTEAD OF INSERT trigger. Here is script

SQL

CREATE TRIGGER CHArraa ON film
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
    INSERT INTO dbo.film
    SELECT 
      special_features,
	CASE WHEN special_features = 'Trailers' THEN rental_rate + 0.10 
             WHEN special_features = 'Commentaries' THEN rental_rate+ 0.50
     	     WHEN special_features = 'Deleted Scenes' THEN rental_rate + 0.20
    	     WHEN special_features = 'Behind the Scenes' THEN rental_rate - 0.20
	END  AS rental_rate
    FROM INSERTED
END