39870

creating before INSERT TRIGGER comparing values between two tables

Question:

I need help creating a before insert trigger, as i am new to TSQL. below are the two tables.

SALARY table:

CREATE TABLE SALARY ( StarName varchar(30) NOT NULL, MovieTitle varchar(30)NOT NULL, MovieYearMade numeric(4, 0) NOT NULL, Amount numeric(8, 0) NULL, PRIMARY KEY (MovieTitle,StarName,MovieYearMade), )

MOVIESTAR table

CREATE TABLE MOVIESTAR ( Name varchar(30) NOT NULL, Address varchar(20), City varchar(15) DEFAULT ('Palm Springs'), Gender char(1) NULL CHECK (Gender ='M' OR GENDER ='F'), BirthYear Numeric(4), PRIMARY KEY CLUSTERED (Name) )

I want to create a trigger so when a new movie is added. It prevents adding SALARY.Amount if SALARY.MovieYearMade is before MOVIESTAR.BirthYear.

I am confused as how to define trigger, when I am comparing values in two tables i.e. SALARY and MOVIESTAR.

thanks,

Answer1:

Are you looking for something like this?

CREATE TRIGGER tg_salary ON salary INSTEAD OF INSERT AS BEGIN INSERT INTO salary (StarName, MovieTitle, MovieYearMade, Amount) SELECT i.StarName, i.MovieTitle, i.MovieYearMade, CASE WHEN i.MovieYearMade < s.BirthYear THEN NULL ELSE i.Amount END FROM INSERTED i JOIN moviestar s ON i.StarName = s.Name END

Here is <strong><a href="http://sqlfiddle.com/#!3/caf99/1" rel="nofollow">SQLFiddle</a></strong> demo

Recommend

  • Should we include sort column, primary key on composite index (MySQL)
  • Access SQL count number of people group by week number
  • SQL language query
  • Iterating Through Table in High-Performance Code
  • PL/SQL: re-write SELECT statement using IN parameter in stored procedure
  • Plot a table with R
  • addressing in assembler
  • Cannot style mat-tab without ::ng-deep and !important
  • FlexJSON Orders Alphabetically by Default
  • SQL - count occurrences of gender
  • Neo4j: Filter nodes based on aggregate function
  • Insertion large number of Entities into SQL Server 2012 [duplicate]
  • What is corresponding c++ data type to SQL numeric(18,0) data type?
  • AlertDialog style when using setView()
  • Time out Error in send mail
  • ApplePay PKPaymentAuthorizationViewController always shows processing
  • Django model inheritance, filtering models
  • Jquery popup on mouse over of calendar control
  • Check for zero lines output from command over SSH
  • Why querying a date BC is changed to AD in Java?
  • Set the selected item in dropdownlist in MVC3
  • Database structure design with variable amounts of fields
  • Meteor: Do Something On Email Verification Confirmation
  • How to clear text inside text field when radio button is select
  • How to avoid particles glitching together in an elastic particle collision simulator?
  • Cannot resolve symbol 'MyApi'
  • Recording logins for password protected directories
  • Why value captured by reference in lambda is broken? [duplicate]
  • How to get address from latitude and longitude android google map v2 [duplicate]
  • Splitting given String into two variables - php
  • AES padding and writing the ciphertext to a disk file
  • angularjs unit test when to use $rootScope.$new()
  • Check if a string to interpolate provides expected placeholders
  • Convert array of 8 bytes to signed long in C++
  • Linker errors when using intrinsic function via function pointer
  • using HTMLImports.whenReady not working in chrome
  • Why joiner is not used after Sequence generator or Update statergy
  • LevelDB C iterator
  • Recursive/Hierarchical Query Using Postgres
  • UserPrincipal.Current returns apppool on IIS