Triggers In Sql

Question

Post reply

Triggers In Sql

Piyushbhatt

Old Hand

Points: 374

March 14, 2023 at 2:07 pm

#4160597

I'm just getting started with SQL Server triggers and I'm having trouble with this problem:

CREATE TRIGGER software_on_install_fill_in
ON software
FOR INSERT, UPDATE
AS
    --declare the names and types of variables you will use
    DECLARE @software_pack char(4), @software_tagnum char(5), 
            @software_datetime datetime, @software_softcost numeric(10,2)
BEGIN
    --set the variables to values
    SET @software_pack = (SELECT software.PACK FROM software, inserted i)
    SET @software_tagnum = (SELECT software.TAGNUM FROM software, inserted i)
    SET @software_datetime = (SELECT software.INSTDATE FROM software, inserted i)

    UPDATE software
    --apply logic to auto update softcost column of inserted row
    SET @software_softcost = (SELECT package.PACKCOST 
                              FROM package, inserted i
                              WHERE SOFTCOST = @software_softcost)
END
GO

-- Try a test case, which fails
insert software(PACK, TAGNUM, INSTDATE)
values('ac11', '32494', '9/14/1998 0:00:01')
GO

--Try to see if contents of the table have changed, also fails
select * from software

Create a trigger to do: once a software package is installed on a PC, the person installing the programme just has to submit data for pack, tagnum, and instdate, according to this page. The softcost column must be changed based on the package's packcost.

I receive the following error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Because the trigger appears to run successfully, I believe something is amiss with my insert statement. Is this what I'm supposed to do?

Gratitude

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 1

Something looks off with that UPDATE that is being done on the table the trigger is on, it's setting a variable instead of updating a column on the table and that subquery is likely returning more than one row.

Also wouldn't this be something better handled at the application level and not the database?

Thom A SSC Guru Points: 99133 More actions · Answer 2

You're assuming an INSERT/UPDATE only ever effects one row; this is completely untrue. You are then trying to assign values to your scalar variables from CROSS JOINed queries. Take your query SET @software_pack = (SELECT software.PACK FROM software, inserted i) for example. This will CROSS JOIN the rows from your table software (some of which you might have just INSERTed) to the rows you just inserted (as these are contained in the psuedo-table inserted). If you INSERTed 3 rows and your table software contained 10 rows (7 prior to the INSERT plus the 3 you just did) then this CROSS JOIN would result in 30 rows.

Looking at what you are doing, I actually don't understand why you want to do it. If the value of the software is stored in the table package, then leave it there; don't duplicate the data. If you need to get the cost then use a JOIN. If you want that information readily available to query, create a VIEW.

This reply was modified 1 year, 9 months ago by Thom A.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

The example posted is the beginning of a RBAR trigger. INSERTs in SQL Server will fire the trigger only once per INSERT even if the INSERT inserts thousands of rows. You MUST write setbased code in the trigger or only one random row of the INSERT will be processed and all the other rows will go unprocessed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 4

It would help if you included the table ddl for package and software.

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 5

I would guess your trigger should look something like this:

CREATE TRIGGER software_on_install_fill_in 
ON software
FOR INSERT, UPDATE
AS BEGIN
    SET NOCOUNT ON

    UPDATE s
       SET s.softcost = p.software_softcost
      FROM software s
     INNER JOIN inserted i
             ON i.SoftwareId = s.SoftwareId
     INNER JOIN package p
             ON p.SoftwareId = s.SoftwareId
END