New need help in writing triggers

[Mun]

Hello All,

I've been writing Oracle triggers, but I'm very much new to SQLServer. Can some one help me in writing Triggers in SQLServer, is it similar to Oracle.

I'm trying to witre a gtrigger on table called TRUCK,when TRK_IS_ACTIVE='F' I want to update the field TRK_STOP_DATE to SYSDATE.


Any help is appreciated,Thanks.

 

[JamesLean]

Is something like what you need?

CREATE TRIGGER tr_truck_upd
  ON truck
  FOR UPDATE
AS

IF UDPATE(trk_is_active)
  UPDATE t
  SET trk_stop_date = getdate()
  FROM truck t JOIN inserted i ON t.truckid = i.truckid
  WHERE i.trk_is_active = 'F'
GO

--James

 

[cheyney]

CREATE TRIGGER triggerUpdateDate ON [dbo].[TRUCK]
FOR INSERT, UPDATE
AS

IF Update(TRK_IS_ACTIVE)
BEGIN
Update [Truck]
SET Truck.TRK_STOP_DATE = getdate
FROM Inserted
WHERE Inserted.PrimaryKeyColumn = Truck.PrimaryKeyColumn
AND Inserted.TRK_IS_AC = 'F'

END

 

[cheyney]

darnit, answer posted while I was writing mine

 

[melontek]

If you are used to Oracle triggers, you will probably not like SQL Server triggers. Here's one I had to write. It's more complicated the the prior examples but will show you how to handle not having the ld and :new bind variables.

Sorry, no comments but hopefully the code is readable. Note that I've learned since that cursors are not always the way to go in TSQL.

Good luck!
[tt]
create TRIGGER Sync_APhoneBook_Magazine_Trigger ON [maginfo]
FOR INSERT, UPDATE, DELETE
AS

Declare @nMagId int
Declare @sMagazineName nvarchar(50)
Declare @cAction nchar(1)

If ( (Exists (Select * From Inserted)) And (Exists (Select * from Deleted)) ) Begin
Set @cAction = 'U'
Declare cUpdated Cursor For
Select i.mag_id, i.mag_name
From Inserted i, Deleted d
Where i.mag_id = d.mag_id

Open cUpdated
Fetch cUpdated Into @nMagId, @sMagazineName
Exec Sync_APhoneBook_Magazine @nMagId, @sMagazineName, @cAction

Close cUpdated
Deallocate cUpdated

End
Else If ( Exists (Select * From Inserted) ) Begin
Set @cAction = 'I'
Declare cInserted Cursor For
Select mag_id, mag_name
From Inserted

Open cInserted
Fetch cInserted Into @nMagId, @sMagazineName
Exec Sync_APhoneBook_Magazine @nMagId, @sMagazineName, @cAction

Close cInserted
Deallocate cInserted
End
Else If ( Exists (Select * From Deleted) ) Begin
Set @cAction = 'D'
Declare cDeleted Cursor For
Select mag_id, mag_name
From Deleted

Open cDeleted
Fetch cDeleted Into @nMagId, @sMagazineName
Exec Sync_APhoneBook_Magazine @nMagId, @sMagazineName, @cAction

Close cDeleted
Deallocate cDeleted
End
[/tt]

 

[Mun]

Thanks for your help, le'me try that code.