Results 1 to 10 of 10

Thread: Making triggers

  1. #1
    Join Date
    May 2013
    Posts
    3

    Unanswered: Making triggers

    Hello,

    Im new with database and I have a question about a table with triggers or maybe a check constraint. Im using SQL Server Management studio.

    I have the following create tables:

    create table bid(
    seller char(10) not null,
    item_nummer numeric(3) not null,
    )

    create table Item(
    startprice char(5) not null,
    description char(22) not null,
    start_date char(10) not null,
    end_date char(10) not null,
    seller char(10) not null,
    item_nummer numeric(3) not null,
    )

    What i'm trying to make is this trigger/constraint:
    colomn "seller" from table Item will get NULL as long as systemdate is > start_date and end_date, then it will get the value from seller from table bid on the same item_nummer in both table).

    I hope i explained correctly what i mean.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I would avoid the use of a trigger in this instance and just place this logic in a stored procedure responsible for populating these tables. I do not know enough about your problem, but it sounds like you might want to run this stored procedure from a SQL Server Agent job.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2009
    Location
    United Kingdom
    Posts
    77
    My basic rule – if business logic needs to be applied use stored procedure and do not apply business logic in Triggers. It is a headache trying to maintain business logic outside of stored procedures

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It's not inappropriate to use triggers for data-level integrity, but the problem here is that you are intending to duplicate the data from table "bid" in table "Item", when the data can (and should) be derived from a join of the two tables.
    For this reason, neither a trigger nor a stored procedure is appropriate in this scenario.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    You will almost never use a TRIGGER

    What I'm trying to make is this trigger/constraint:
    column "seller_duns" from table Item will get NULL as long as “CURRENT_TIMESTAMP BETWEEN auction_start_date AND auction_end_date”, then it will get the value from seller_duns from Bids on the same item_nbr in both table). I hope I explained correctly what I mean.
    The explanation is good; the design is wrong.

    1. You need to read a lot of the basics. The goal of RDBMS is to reduce or remove redundancy; you want to increase by having the same fact in two places. Sellers aer on table and you reference them; NO TRIGGERS!
    2. You need to learn standards. In particular, start with ISO-11179 so you will understand what a data element is.
    3. Every table has to have a key.
    4. A table is a set, so its name is a collective or plural noun.
    5. The DUNS is the industry standard for identifying a company and it is nine digits.
    6. Prices are DECIMAL, not strings.
    7. Most of the work in SQL is in the DDL, not triggers or other procedural code.
    8. Sellers also require buyers for a bid! And a bid amount with a timestamp
    9. Relationships also have tables. You can have n-ary relationships, like buyer-seller-item (called a bid).

    CREATE TABLE Sellers
    (seller_duns CHAR(9) NOT NULL PRIMARY KEY
    CHECK (seller_duns LIKE
    '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
    …);

    CREATE TABLE Buyers
    (buyer_duns CHAR(9) NOT NULL PRIMARY KEY
    CHECK (Buyer_duns LIKE
    '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
    …);

    CREATE TABLE Items
    (item_nbr DECIMAL (3,0) NOT NULL PRIMARY KEY,
    start_price DECIMAL(8,2) NOT NULL
    CHECK (start_price >= 0.00),
    item_description CHAR(25) NOT NULL,
    auction_start_date DATE NOT NULL,
    auction_end_date DATE NOT NULL,
    CHECK (auction_start_date <= auction_end_date),
    seller_duns CHAR(9) NOT NULL
    REFERENCES Sellers(seller_duns));

    CREATE TABLE Bids
    (seller_duns CHAR(9) NOT NULL
    REFERENCES Sellers(seller_duns),
    buyer_duns CHAR(9) NOT NULL
    REFERENCES Buyers(buyer_duns),
    item_nbr NUMERIC(3,0) NOT NULL
    REFERENCES Items,
    PRIMARY KEY (seller_duns, buyer_duns, item_nbr),
    bid_timestamp DATETIME2(0) NOT NULL,
    bid_amt DECIMAL(8,2) NOT NULL);

    Now, what do you want to know about this auction?

  6. #6
    Join Date
    May 2013
    Posts
    3
    Thank you everyone for the replies.

    @Celko
    It is a big project for school and one of the rules was the above mention one:
    colomn "seller" from table Item will get NULL as long as systemdate is > start_date and end_date, then it will get the value from seller from table bid on the same item_nummer in both table).

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    My more pedantic responders here would say that you should never let someone hand you as the developer a solution. Just the problem. And then you should fix it with the expertise they are paying you for.

    My concern was not about business logic. What code is not business logic in a business application. Mine was more of a concern for a inexperienced sql developer writing triggers and the mistakes they usually make.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Swirl_ View Post
    It is a big project for school and one of the rules was the above mention one:
    colomn "seller" from table Item will get NULL as long as systemdate is > start_date and end_date, then it will get the value from seller from table bid on the same item_nummer in both table).
    I suggest you direct your instructor to this forum. Whoever dreamed up this assignment has a lot to learn about database design and best practices. What they are teaching you to do is bad.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Hi Swirl,

    You have presented us with quite a problematic description. It is hard to get started where it is flawed.
    I have made an attempt to show you how to solve your problem. But you will have to do a lot of rework about the database design and present all the inconsistencies to whoever gave you this assignment.

    colomn "seller" from table Item will get NULL as long as systemdate is > start_date and end_date, then it will get the value from seller from table bid on the same item_nummer in both table).
    Let's look at this rule:
    1) The seller of an item must be set based on the bids. There is no logic behind this. When I bring my vintage car to an auction, it is known from the moment the Item "Vintage car" is entered in the system that I am the seller. It is the Buyer who will only be known after the auction period.

    The column seller is mandatory (NOT NULL), so we have to assign it a value when we create a record.

    2) We may not update a column in a table as long as the system date is higher than the start_date and the end_date. So, all the items from the past may not be updated.
    When can we update that column? When the start_date or end_date are in the future. So we can not update the Buyer on the last day of the actiuon

    3) in an auction, multiple (potential) buyers can bid an amount on an item. The same person can make multiple bids. What to do with multiple potential buyers? I used the logic, the one with the highest bid wins. What is the highest bid is lower than the minimum price set by the seller? I found the column startprice. I assume this holds the minimum price of the item, set by the seller. Bids lower than startprice can never win the auction.

    As I stated before, it is hard to get started where this assignment it is flawed. They are all over.

    Perhaps this will give you a start on how to tackle this problem. You should also read Celko's post, his data model is a lot more sound than what you presented.

    I have only changed the most blatant flaws, like storing dates or amounts in strings, or changing the column seller into "buyer" in the bid table. I have also added a column buyer to the Item table, though that is a denormalisation. This should be calculated in a query by the database system, not stored in a column.

    Code:
    create table Item(
    	item_number	numeric(3)		not null,
    	startprice	numeric(5,2)	not null,
    	description	char(22)		not null,
    	start_date	DATE			not null,
    	end_date	DATE			not null,
    	seller		char(10)		not null,
    	buyer		CHAR(10)		NULL,
    	CONSTRAINT pk_item PRIMARY KEY (item_number)
    )
    
    create table bid(
    	buyer		char(10)		not null,
    	item_number	numeric(3)		not null,
    	bid_amount	numeric(5,2)	not null,
    	CONSTRAINT pk_bid PRIMARY KEY (buyer, item_number)
    )
    
    ALTER TABLE bid
    	ADD CONSTRAINT fk_bid_item FOREIGN KEY (item_number)
    		REFERENCES Item(item_number)
    
    INSERT INTO Item(item_number, startprice, description, start_date, end_date, seller) VALUES
    (1, 1.50, 'something old', '2000-01-01', '2000-12-31', 'the Seller'),
    (2, 5.00, 'something new', '2013-05-01', '2013-05-24', 'the Seller'),
    (3, 10.00, 'something blue', '2013-05-01', '2013-05-30', 'the Seller')
    
    INSERT INTO bid(buyer, item_number, bid_amount) VALUES
    ('Me', 1, 1.5),
    ('Myself', 1, 1.5),
    ('I', 1, 1.5),
    
    ('Me', 2, 1.25),
    ('Myself', 2, 2.50),
    
    ('Me', 3, 100.50),
    ('Myself', 3, 101.00),
    ('I', 3, 50.00)
    
    SELECT * FROM Item
    SELECT * FROM bid ORDER BY item_number, buyer
    
    DECLARE @current_date	DATE
    --SET @current_date = '1999-01-01'
    --SET @current_date = '2000-02-01'
    --SET @current_date = '2001-02-01'
    --SET @current_date = '2013-05-25'
    --SET @current_date = '2014-01-01'
    SET @current_date CAST(GETDATE() AS date)
    
    -- use a CTE (highest_bid) to hold all the bids for an item.
    -- Order them by bid price, highest first. If you select the first one, 
    -- with RowNum = 1, you get the highest bid price. When multiple people 
    -- bid the same price, a random person is selected as the auction winner.
    ;WITH highest_bid AS(
    SELECT item_number,
    	bid.buyer,
    	bid.bid_amount,
    	ROW_NUMBER() OVER (PARTITION BY Item_number ORDER BY bid_amount DESC) as RowNum
    FROM bid
    ) 
    UPDATE U
    SET buyer = HB.buyer
    FROM Item as U
    	INNER JOIN highest_bid as HB ON
    		U.item_number = HB.item_number
    		AND RowNum = 1
    WHERE @current_date > U.start_date AND
    	@current_date > U.end_date AND
    	U.startprice <= HB.bid_amount
    
    SELECT * FROM Item
    SELECT * FROM bid ORDER BY item_number, buyer
    
    DROP table bid
    DROP table Item
    This is not a trigger as you asked. The update should run each night one second after midnight. A trigger will not get fired by a clock, only by actions on a table in a database (like an INSERT, UPDATE or DELETE event). So a trigger is useless.

    You could run the UPDATE code automated every day (or manually, first thing to do in the morning is: get up, log in into system, run update script, log out, eat breakfast, ... )

    Yet it's far better to use a query to get the correct values from the database. No need for updates, triggers, timers, ....
    Code:
    ;WITH highest_bid AS(
    SELECT item_number,
    	bid.buyer,
    	bid.bid_amount,
    	ROW_NUMBER() OVER (PARTITION BY Item_number ORDER BY bid_amount DESC) as RowNum
    FROM bid
    )
    SELECT I.item_number, I.seller, HB.buyer, HB.bid_amount as price_sold, I.description, I.start_date, I.end_date
    FROM Item as I
    	LEFT OUTER JOIN highest_bid as HB ON
    		I.item_number = HB.item_number AND 
    		I.end_date < CAST(GETDATE() AS DATE) AND
    		I.startprice <= COALESCE(HB.bid_amount, I.startprice) AND
    		HB.RowNum = 1
    Last edited by Wim; 05-24-13 at 09:57.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    May 2013
    Posts
    3
    Hello,

    Thank you all so much all for your replies!
    Helped me alot with this.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •