Update Trigger 1

[klewis10367]

I am running this trigger to update a field in a table when that field is changed on the front end. When I hit save no error pops up but the field does not change. Permissions are correct. Any Ideas.

CREATE TRIGGER [trgUpdateUPR40300_kml] ON [dbo].[UPR40300]
for insert
AS
if update (dscriptn)
begin
update gpviews.dbo.upr40300
set dscriptn = a.dscriptn from gpviews.dbo.up40300 b inner join inserted a on
b.deprtmnt=a.deprtmnt where b.dbase='two'
end

 

[clapag22]

You say, "when that field is changed on the front end" but your trigger appears to be an insert trigger only. Therefore it only fires when the row is inserted:

CREATE TRIGGER [trgUpdateUPR40300_kml] ON [dbo].[UPR40300] 
for  insert

Try this:

CREATE TRIGGER [trgUpdateUPR40300_kml] ON [dbo].[UPR40300] 
for  update

Or am I missing something?

 

[klewis10367]

That did it. Thanks. Also, any idea how I would change it to a delete trigger. Basicall what I am trying to do is replicate one database to another using triggers.

 

[Luzian]

create a delete trigger

CREATE TRIGGER [trgDeleteUPR40300_kml] ON [dbo].[UPR40300]
FOR DELETE

 

[clapag22]

If you're doing the same thing regardless of update or delete you could just do this:

CREATE TRIGGER [trgUpdateUPR40300_kml] ON [dbo].[UPR40300] 
for  update, delete

If you need it do something different you'd want to create a separate trigger starting like this:

CREATE TRIGGER [trgDeleteUPR40300_kml] ON [dbo].[UPR40300] 
for  delete

Good luck!
Craig

 

[klewis10367]

I want it seperate. What would be the script? I am relatively new at triggers and I can not seem to get the syntax. I would be using the same info as the update.

 

[clapag22]

If you're trying to do the same thing, joining to the deleted table instead of the inserted table should give you what you need. You also don't need the "If Update" logic. Just the update statement itself.

 

[klewis10367]

I actually want to remove the deleted info from the destination table, I do not want to know what was deleted as in an Audit table.

 

[clapag22]

Ohhh... Well, following your logic from above it would be something like this (I'm assuming deprtmnt is your PK):

CREATE TRIGGER [trgDeleteUPR40300_kml] ON [dbo].[UPR40300] 
for  delete
AS

delete
from gpviews.dbo.upr40300
where dbase='two'
and deprtmnt in (select deprtmnt from deleted)