Trigger. Why is this not working? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Trigger. Why is this not working?

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-02-16 : 12:32:51

Hello,

I have a table as follows:


create table dbo.Files
(
	FileID uniqueidentifier ROWGUIDCOL not null 
		default NewID()
	  constraint PK_File primary key clustered,
	Description nvarchar(800),
	Path nvarchar(800) not null
)

When I insert or update a record I, sometimes, include $Guid$ in the Path value. When that happens I want $Path$ to be replaced by the FileID Guid.

I created the following Trigger (I think I should use a Trigger):

CREATE Trigger FilePath ON Files
FOR INSERT, UPDATE

-- Replace filename by FileID
REPLACE(Path, '$Guid$', FileID)

GO

This is not working.
I don't get any error but the value is not replaced.

What am I doing wrong?

Thanks,
Miguel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-16 : 12:45:37

make it like this & try:-

CREATE Trigger FilePath ON Files
FOR INSERT, UPDATE
IF EXISTS (SELECT * FROM INSERTED)
-- Replace filename by FileID

UPDATE f
SET f.Path=REPLACE(Path, '$Guid$', CAST(FileID AS varchar(50)))
FROM Files f
INNER JOIN INSERTED i
ON i.FileID=f.FileID
WHERE PATINDEX('%$Guid$%',i.Path)>0

GO

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-02-16 : 13:16:07

I get the error on your code:

Incorrect syntax near the keyword IF.

I also realized that I am getting a similar error:
Incorrect syntax near the keyword REPLACE.

What am I missing?

Thanks,
Miguel

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-02-16 : 13:21:51

Ok, I tried the following (Added the AS):

CREATE Trigger FilePath ON Files
FOR INSERT, UPDATE
AS
IF EXISTS (SELECT * FROM INSERTED)

But now I am getting the same error as in my code:
Incorrect sintax near replace.

What is wrong?

Thanks,
Miguel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-16 : 13:26:58

quote:
Originally posted by shapper

Ok, I tried the following (Added the AS):

CREATE Trigger FilePath ON Files
FOR INSERT, UPDATE
AS
IF EXISTS (SELECT * FROM INSERTED)

But now I am getting the same error as in my code:
Incorrect sintax near replace.

What is wrong?

Thanks,
Miguel

try like this

CREATE Trigger FilePath ON Files
AFTER INSERT, UPDATE
AS
IF EXISTS (SELECT * FROM INSERTED)
-- Replace filename by FileID
BEGIN
UPDATE f
SET f.Path=REPLACE(i.Path, '$Guid$', CAST(i.FileID AS varchar(50)))
FROM Files f
INNER JOIN INSERTED i
ON i.FileID=f.FileID
WHERE PATINDEX('%$Guid$%',i.Path)>0
END
GO

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-02-16 : 13:37:36

Ok,

Now I am getting an error when I insert the record:

The target table 'dbo.Files' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

I am using a LinqDataSource with a ListView to insert the record.

Can this be from using a LinqDataSource instead of a Stored Procedure?

Thanks,
Miguel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-16 : 13:45:06

OUTPUT clause? are you using OUTPUT clause in your query? Can i see your full query then?

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-02-16 : 14:13:51

That's the point I am using a LinqDataSource and I am not defining the "LINQ queries" ... I am trying to figure what is LinqDataSource sending to SQL when inserting a record but until now I wasn't able to figure it out ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-16 : 14:21:49

I'm not too familar with LingDataSource. I'm a database developer and dont deal much with front end. It seeme like you are inserting and simultaneously taking inserted values using OUTPUT clause to some other table or variable. In such cases, you cant have a trigger enabled on table as complained by query analyser.

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-02-16 : 14:27:59

Hi,

I suspect that it is the true because in the Inserted event of the LinqDataSource I can have access to the inserted values.

So on the database side do I have any other option to do this?

Thanks,
Miguel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-16 : 23:00:20

You can alternatively INSERT the data and then provide an UPDATE to replace the $guid$ with fileID.Hope you will be able to retrieve the last inserted ID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-17 : 04:27:46

Why use
IF EXISTS (SELECT * FROM INSERTED)
at all? INSERTED virtual table always exists for UPDATE and INSERT operation.

I also think a trigger don't care how and from where the data is updated or inserted from. A trigger monitors the current table.

CREATE TRIGGER trgFilePath ON Files
FOR INSERT, UPDATE
AS

UPDATE		f
SET		f.Path = REPLACE(f.Path, '$Guid$', CAST(f.FileID AS NVARCHAR(40)))
INNER JOIN	INSERTED AS i ON i.FileID = f.FileID
WHERE		i.Path LIKE '%$Guid$%'

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-17 : 08:45:54

quote:
Originally posted by Peso

Why use
IF EXISTS (SELECT * FROM INSERTED)
at all? INSERTED virtual table always exists for UPDATE and INSERT operation.

I also think a trigger don't care how and from where the data is updated or inserted from. A trigger monitors the current table.

CREATE TRIGGER trgFilePath ON Files
FOR INSERT, UPDATE
AS

UPDATE f
SET f.Path = REPLACE(f.Path, '$Guid$', CAST(f.FileID AS NVARCHAR(40)))
INNER JOIN INSERTED AS i ON i.FileID = f.FileID
WHERE i.Path LIKE '%$Guid$%'

E 12°55'05.25"
N 56°04'39.16"

Yup thats true. there's no necessity of IF check here

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-17 : 10:17:56

a FROM clause would be helpful

quote:
Originally posted by Peso

Why use
IF EXISTS (SELECT * FROM INSERTED)
at all? INSERTED virtual table always exists for UPDATE and INSERT operation.

I also think a trigger don't care how and from where the data is updated or inserted from. A trigger monitors the current table.

CREATE TRIGGER trgFilePath ON Files
FOR INSERT, UPDATE
AS

UPDATE f
SET f.Path = REPLACE(f.Path, '$Guid$', CAST(f.FileID AS NVARCHAR(40)))
From Files f
INNER JOIN INSERTED AS i ON i.FileID = f.FileID
WHERE i.Path LIKE '%$Guid$%'

E 12°55'05.25"
N 56°04'39.16"

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-17 : 16:20:10

Good spot!
My mistake. I should have seen that

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources