Triggers - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Triggers

Author

Topic

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-05-13 : 08:02:49

Hi, i need to create a trigger so that if a particular value is changed in a column, it needs to change back to a preset value. IE: the value for a particular record should not be changed. How do i do this. What type of trigger do i use and how do i write it.

Can someone please help me with this

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2009-05-13 : 08:07:35

http://www.sqlteam.com/article/an-introduction-to-triggers-part-i

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 10:00:20

quote:
Originally posted by sgandhi

Hi, i need to create a trigger so that if a particular value is changed in a column, it needs to change back to a preset value. IE: the value for a particular record should not be changed. How do i do this. What type of trigger do i use and how do i write it.

Can someone please help me with this

isnt it enough to put a check constraint on the column?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-13 : 12:11:24

I guess it depends on how many UserIDs you are dealing with. It's probly best to use a trigger and a store the UserIDs in a table so you can just join to it. But, if it is only a few you could add a check constraint:

CREATE TABLE People 
(
	UserID VARCHAR(10) NOT NULL , 
	DemoID CHAR(1) NOT NULL
)
GO

ALTER TABLE People WITH CHECK 
	ADD CONSTRAINT CK_People_DemoID CHECK 
	(
		(UserID IN ('User1', 'User2') AND DemoID = 'N')
		OR (UserID NOT IN ('User1', 'User2'))
	)
GO

INSERT People
SELECT 'User1', 'Y'

INSERT People
SELECT 'User1', 'N'

INSERT People
SELECT 'User2', 'N'

INSERT People
SELECT 'User2', 'Y'

INSERT People
SELECT 'User3', 'N'

INSERT People
SELECT 'User3', 'Y'

UPDATE People
SET DemoID = 'Y'
WHERE UserID = 'User1'

SELECT *
FROM People


DROP TABLE People

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-05-13 : 13:01:31

Can you give me an example of how i can write this in a trigger. There is already an existing table with the users in it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 13:06:59

shouldnt it be?

ALTER TABLE People WITH CHECK 
	ADD CONSTRAINT CK_People_DemoID CHECK 
	(
		((UserID ='test1' AND DemoID IN ('Y','N'))
		OR (UserID='test2' AND DemoID='N'))
	)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-13 : 13:16:10

quote:

Originally posted by visakh16

shouldnt it be?

ALTER TABLE People WITH CHECK 
	ADD CONSTRAINT CK_People_DemoID CHECK 
	(
		((UserID ='test1' AND DemoID IN ('Y','N'))
		OR (UserID='test2' AND DemoID='N'))
	)

For the exmaple the OP provided yeah, But, since the example is not the complete dataset and requirements, I was giving an example of how it could be done. That way the OP could expand the example meet thier needs.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 13:24:27

yeah...sorry i was reading it with OP's explanation

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-13 : 13:44:34

Since you only need to deal with 3 special "Users" a Check Constraint is probably the better idea. However, here is one way to implement a trigger:

-- Create DDL and Sample Data
CREATE TABLE People 
(
	UserID VARCHAR(10) NOT NULL , 
	DemoID CHAR(1) NOT NULL
)
GO

CREATE TABLE NoPeople (UserID VARCHAR(10) NOT NULL PRIMARY KEY)

INSERT NoPeople
SELECT 'User1'
UNION ALL SELECT 'User2'

-- Create Trigger
CREATE TRIGGER TrigPeople ON People
FOR INSERT, UPDATE
AS
BEGIN

	DECLARE @AffectedRows INT

	SELECT 
		@AffectedRows = COUNT(*)
	FROM 
		Inserted  AS I
	INNER JOIN
		NoPeople AS P
		ON I.UserID = P.UserID
	WHERE
		I.DemoID <> 'N'
		
	IF @AffectedRows <> 0
	BEGIN
		RAISERROR ('DemoID must be "N".', 16, 1)
		ROLLBACK TRAN
	END
END

-- Test Trigger

INSERT People
SELECT 'User1', 'Y'

INSERT People
SELECT 'User1', 'N'

INSERT People
SELECT 'User2', 'N'

INSERT People
SELECT 'User2', 'Y'

INSERT People
SELECT 'User3', 'N'

INSERT People
SELECT 'User3', 'Y'

UPDATE People
SET DemoID = 'Y'
WHERE UserID = 'User1'

SELECT *
FROM People

DROP TRIGGER TrigPeople
DROP TABLE NoPeople
DROP TABLE People

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-15 : 13:31:22

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources