trigger problem. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

trigger problem.

Author

Topic

argon007
Starting Member

38 Posts

Posted - 2008-06-14 : 01:15:21

quote:
ensure the difference between the minimum job level (min_lvl) and maximum job level (max_lvl) is not greater than 100 when rows are inserted or updated in the job table.

CREATE TRIGGER jobs_ins_upd
ON jobs
FOR UPDATE, INSERT
AS
IF ( SELECT difference (max_lvl, min_lvl) FROM jobs ) > 100
BEGIN
PRINT 'ERROR: The difference between max_lvl and min_lvl can not exceed 100.'
ROLLBACK TRANSACTION
END

when i run this, the error is happened

INSERT jobs 
VALUES( 'blahblahblahblah', 20, 300 )

quote:
Msg 512, Level 16, State 1, Procedure jobs_ins_upd, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-14 : 01:21:16

[code]
CREATE TRIGGER jobs_ins_upd
ON jobs
FOR UPDATE, INSERT
AS
IF ( SELECT difference (max_lvl, min_lvl) FROM ~~jobs~~ Inserted ) > 100
BEGIN
PRINT 'ERROR: The difference between max_lvl and min_lvl can not exceed 100.'
ROLLBACK TRANSACTION
END
[/code]

And This would only work if you are going to insert only one record at the time, else you require to use the loop for checking through various records.

its recommended to use Check Constraint for this kind logics then trigger

Chirag

http://www.chirikworld.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-14 : 01:35:25

or use an INSTEAD of trigger:-

CREATE TRIGGER jobs_ins_upd
ON jobs
INSTEAD OF  UPDATE, INSERT
AS
IF EXISTS( SELECT 1 FROM INSERTED) AND EXISTS (SELECT 1 FROM DELETED)
BEGIN
UPDATE j
SET j.Field1=i.Field1,
....
FROM jobs j
INNER JOIN INSERTED i
ON i.PK=j.PK
WHERE difference (i.max_lvl, i.min_lvl)<=100
END

IF EXISTS( SELECT 1 FROM INSERTED) AND NOT EXISTS (SELECT 1 FROM DELETED)
BEGIN 
INSERT INTO jobs (field1,field2,...)
SELECT field1,field2,...
FROM INSERTED i
WHERE difference (i.max_lvl, i.min_lvl)<=100
END

Where PK is your primary key and field1,field2 are fields...

argon007
Starting Member

38 Posts

Posted - 2008-06-14 : 01:39:27

what about updated? i mean

IF ( SELECT difference (max_lvl, min_lvl) FROM Inserted  ) > 100

should i add the updated?

like this:

IF ( SELECT difference (max_lvl, min_lvl) FROM Inserted, Updated  ) > 100

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-14 : 02:08:16

quote:
Originally posted by argon007

what about updated? i mean

IF ( SELECT difference (max_lvl, min_lvl) FROM Inserted ) > 100

should i add the updated?

like this:

IF ( SELECT difference (max_lvl, min_lvl) FROM Inserted, Updated ) > 100

Nope you dont have any Updated table. Inserted and Deleted are the internal temporary tables used by SQL server for performing insert,update and delete operations. In case of insert, you will have inserted table containing values to be inserted. In case of update, inserted table will contain values to be updated and deleted will contain old values of field. In case of delete, deleted will contain the values which are to be deleted.I have used the IF loops to distinguish b/w insert and updates

IF EXISTS( SELECT 1 FROM INSERTED) AND EXISTS (SELECT 1 FROM DELETED)
here we check if both INSERTED & DELETED contain records which will happen only for update

IF EXISTS( SELECT 1 FROM INSERTED) AND NOT EXISTS (SELECT 1 FROM DELETED)
here we check if we have records only in INSERTED and not in DELETED which means its an insert operation.

argon007
Starting Member

38 Posts

Posted - 2008-06-17 : 23:23:20

[code]
CREATE TRIGGER jobs_ins_upd
ON jobs
FOR UPDATE, INSERT
AS
IF ( SELECT max_lvl - min_lvl FROM Inserted ) > 100
BEGIN
PRINT 'ERROR: The difference between max_lvl and min_lvl can not exceed 100.'
ROLLBACK TRANSACTION
END
[/code]
ERROR: The difference between max_lvl and min_lvl can not exceed 100.
--OK.

but the following is error.

quote:
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

what should i do?

add 'go' to end transaction?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 00:56:12

Thats beciase the trigger rolled back the transaction:-

http://books.google.co.in/books?id=5_AEiJXbyiEC&pg=PA323&lpg=PA323&dq=The+transaction+ended+in+the+trigger.+The+batch+has+been+aborted+t-sql&source=web&ots=tRwmtBdid0&sig=pgWzrebFtyX2bfZjf20nQM2XX0M&hl=en&sa=X&oi=book_result&resnum=3&ct=result#PPA323,M1

argon007
Starting Member

38 Posts

Posted - 2008-06-18 : 17:52:55

thank you so much. problem solved.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources