Need some help with the trigger urgent please.

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

SQL Server 2008 Forums

Transact-SQL (2008)

Need some help with the trigger urgent please.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-04-20 : 13:02:05

[code]Hi,

I have a trigger need to twist to prevent updating duplicate values. Business rules and desire result are below.

Business rule: 1. Duplicate values in drawerno, DrawerStoreNo, DrawerRegisterNo are NOT allowed, but NULL is O.K
2. Update these 3 columns to existing values are not allowed.

The problem I have now is how can I change the trigger to check for if UPDATE and the values already existing.
This could be simple to add an UPDATE in a trigger but some reasons I got stuck.
Please see the desire result below.

Thank you in advance.

-----------------------------------------------------------------------------------
IF OBJECT_ID('Phrmcst', 'u') IS NOT NULL
DROP TABLE dbo.Phrmcst
GO
CREATE TABLE dbo.Phrmcst
(
PKey INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
drawerno INT NULL,
DrawerStoreNo INT NULL,
DrawerRegisterNo INT NULL,
UserId VARCHAR(10) NULL
)
GO

INSERT Phrmcst (drawerno, DrawerStoreNo, DrawerRegisterNo )
VALUES (1, 1, 1), ( 2, 3, NULL), (NULL, NULL, NULL), (NULL, NULL, NULL);
GO

SELECT *
FROM Phrmcst
GO

PKey drawerno DrawerStoreNo DrawerRegisterNo UserId
----------- ----------- ------------- ---------------- ----------
1 1 1 1 NULL
2 2 3 NULL NULL
3 NULL NULL NULL NULL
4 NULL NULL NULL NULL

--------------------------------------------------------------------------
-- Trigger.

IF NULLIF (OBJECT_ID('dbo.rg_ins_Phrmcst'), 0) > 0
DROP TRIGGER dbo.trg_ins_Phrmcst;
GO

CREATE TRIGGER dbo.trg_ins_Phrmcst
ON dbo.Phrmcst
FOR INSERT
AS
SET NOCOUNT ON;

BEGIN

IF EXISTS ( SELECT 1
FROM dbo.Phrmcst AS a
JOIN INSERTED AS i
ON a.drawerno = i.drawerno
AND a.DrawerStoreNo = i.DrawerStoreNo
AND a.DrawerRegisterNo = i.DrawerRegisterNo
HAVING COUNT(1) > 1
)
BEGIN
RAISERROR ('Cannot enter duplicate values. Please check again.', -1, 1) WITH NOWAIT
PRINT ' '
ROLLBACK TRANSACTION
--PRINT 'Transaction is Rolling Back.'
--PRINT 'Trancount count: ' + CAST(@@Trancount AS CHAR(2))
RETURN

END
END
GO

--------------------------------------------------------------------------

-- Testing Update...
DECLARE @drawerno INT = 1
DECLARE @DrawerStoreNo INT = 1
DECLARE @DrawerRegisterNo INT = 1

-- How can I get an error, if the values already exist. Something like Values already exists.
UPDATE Phrmcst
SET drawerno = @drawerno,
DrawerStoreNo = @DrawerStoreNo,
DrawerRegisterNo = @DrawerRegisterNo
WHERE PKey = 4;
go

SELECT *
FROM Phrmcst
GO

-- DELETE T1 WHERE PKey > 3;

EXECute sp_depends Phrmcst

-- DROP TRIGGER trg_test1

DECLARE @drawerno INT = 1
DECLARE @DrawerStoreNo INT = 1
DECLARE @DrawerRegisterNo INT = 1

DECLARE @drawerno INT
DECLARE @DrawerStoreNo INT
DECLARE @DrawerRegisterNo INT

--SELECT @drawerno
--SELECT @DrawerStoreNo
--SELECT @DrawerRegisterNo

INSERT Phrmcst ( drawerno, DrawerStoreNo, DrawerRegisterNo )
SELECT @drawerno, @DrawerStoreNo, @DrawerRegisterNo
GO [/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-20 : 15:09:58

i think you need a GROUP BY inside your IF EXISTS to group the data on three columns specified and then check for the duplicates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources