June 22, 2015 at 9:19 am
I am making a SQL database for a shop and i am have a lil problem in functions and triggers so i really hope someone here can help me out
what i need is
to create a function [GetDiscount] to get the DISCOUNT_RATE for a particular PROD_CAT from the Discount Table. along with setting an exception when DISCOUNT_RATE is not found for particular PROD_CAT
to create a Trigger [TrgDisAmount] to read PROD_NAME and Price from Product Table and to calculate the DISCOUNT and AMOUNT of SALES Table fr evert new row with Serial and PCODE
but for the TRIGGER
i need to enter the values of PCODE and Serial only, rest should be entered automatically using the trigger
June 22, 2015 at 9:20 am
Need more information.
Table definitions please, sample data, examples of the data modifications that will fire the triggers and details of what you want the triggers to do to the data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2015 at 9:22 am
respected sir,
i dont know how to insert image from my computer to show you the sample data in the tables. can i email you? can i get your email id?
June 22, 2015 at 9:28 am
Post the information here. Not as an image, as something people who are interested in helping you can copy, run and use to test any code they write.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2015 at 9:33 am
CREATE TABLE PRODUCT
PCODE VARCHAR2(8) PRIMARY KEY,
PROD_NAME VARCHAR2(20) NOT NULL,
PROD_CAT VARCHAR2(10) NOT NULL,
PRICE NUMBER(9,3));
CREATE TABLE DISCOUNT
PROD_CAT VARCHAR2(10) PRIMARY KEY,
DISCOUNT_RATE NUMBER(6) NOT NULL
);
CREATE TABLE SALES
SLNO NUMBER(4) PRIMARY KEY,
PCODE VARCHAR2(8) NOT NULL,
PROD_NAME VARCHAR2(20) NOT NULL,
PRICE NUMBER(9,3),
DISCOUNT NUMBER(9,3),
AMOUNT NUMBER(9,3));
);
June 22, 2015 at 9:37 am
That's not SQL Server code.
This is a Microsoft SQL Server site, we're not going to be much help in writing triggers and functions for Oracle. Maybe try an Oracle-focused site?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply