IF EXISTS In Trigger - 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)

IF EXISTS In Trigger

Author

Topic

Help_Me
Starting Member

3 Posts

Posted - 2012-01-31 : 21:16:49

ALTER TRIGGER [oil].[UpdateTest]
ON [oil].[T_DIST]
AFTER INSERT
AS

IF EXISTS (SELECT Subject, REC_NO FROM oil.T_DIST WHERE REC_NO=(select max(REC_NO) from oil.T_DIST) AND SUBJECT = 'CONCRETE DRAWING')

BEGIN
INSERT INTO dbo.Test (Test)
VALUES ('Test')
END

This trigger runs fine, but I need to run another SQL Query against the IF EXISTS, "SELECT TITLE FROM oil.O_DESD WHERE TITLE = 'String'"

So basically, I want a trigger that only executes IF both the SELECT Subject and SELECT Title queries return a result and "exist". Pretty much as follows (I know this is incorrect and doesn't work):

ALTER TRIGGER [oil].[UpdateTest]
ON [oil].[T_DIST]
AFTER INSERT
AS

IF EXISTS (SELECT Subject, REC_NO FROM oil.T_DIST WHERE REC_NO=(select max(REC_NO) from oil.T_DIST) AND SUBJECT = 'CONCRETE DRAWING')
AND (SELECT TITLE FROM oil.O_DESD WHERE TITLE = 'String')

BEGIN
INSERT INTO dbo.Test (Test)
VALUES ('Test')
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-31 : 21:26:09

Nest them:


IF EXISTS (SELECT * FROM oil.T_DIST WHERE REC_NO=(select max(REC_NO) from oil.T_DIST) AND SUBJECT = 'CONCRETE DRAWING')
BEGIN
	IF EXISTS (SELECT * FROM oil.O_DESD WHERE TITLE = 'String')
	BEGIN
		INSERT INTO dbo.Test (Test)
		VALUES ('Test')
	END
END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-31 : 21:27:11

Actually, just this even:


IF EXISTS (SELECT * FROM oil.T_DIST WHERE REC_NO=(select max(REC_NO) from oil.T_DIST) AND SUBJECT = 'CONCRETE DRAWING') 
AND EXISTS (SELECT * FROM oil.O_DESD WHERE TITLE = 'String')
	INSERT INTO dbo.Test (Test)
	VALUES ('Test')

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Help_Me
Starting Member

3 Posts

Posted - 2012-01-31 : 22:19:39

Great thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-31 : 22:26:29

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources