Simple SQL 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

SQL Server 2005 Forums

Transact-SQL (2005)

Simple SQL Trigger Problem

Author

Topic

Help_Me
Starting Member

3 Posts

Posted - 2012-01-30 : 00:19:11

CREATE TRIGGER TestUpdate
ON oil.T_DIST
AFTER INSERT
AS
IF (oil.T_DIST.SUBJECT = 'CONCRETE DRAWING' AND oil.O_DESD.TITLE = 'CONCRETE DRAWING')
BEGIN
INSERT INTO dbo.Test (Test)
VALUES ('Test')
END

Basically, what im trying to do is if the Subject column in the T_DIST table is 'CONCRETE DRAWING' AND the TITLE column in the O_DESD table is 'CONCRETE DRAWING' then insert a new record into dbo.Test

Whats wrong with the IF statement?

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-01-30 : 00:51:45

You can not use oil.T_DIST.SUBJECT or oil.O_DESD.TITLE in IF Statement..

You can get inserted value like SELECT * FROM Inserted table. at the same time you used O_DESD table, This is not possible to get two table's inserted values at the same time.

You can get only Triggered Table values using INSERTED table.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-30 : 11:44:35

it should be


CREATE TRIGGER TestUpdate
ON oil.T_DIST 
AFTER INSERT
AS
BEGIN
IF EXISTS(SELECT 1
FROM INSERTED WHERE SUBJECT = 'CONCRETE DRAWING' 
AND TITLE = 'CONCRETE DRAWING')
BEGIN
INSERT INTO dbo.Test (Test)
VALUES ('Test')
END
END

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources