help to create 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

General SQL Server Forums

New to SQL Server Programming

help to create trigger

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-12-21 : 01:23:15

Dear All,

I have a table which i am updating from back end.As datemodifed column is having a default constraint when user inserts data from front end application it will get insert latest value.But when i am updating the specific column its not getting updated.

So i am planning to create the trigger on this table to get updated for the same table.

My update script like this:

update HCP set isactive = 1, activeindicator = 1, inactivereason_id = null,DateModified=getdate()
where (isactive <> 1 or activeindicator <> 1) and legacyID in (select DMID from Atlantis_Staging_Doctor where Rectype <> 'D')

table schema:

Column_name Type
ID int
DateModified datetime
LegacyID nvarchar
Category_ID int
Title_ID int
NameGiven nvarchar
NameMiddle nvarchar
NameFamily nvarchar
NameLocale_ID int
Position_ID int
Gender_ID int
ActiveIndicator int
HCPIDNumber nvarchar
InactiveReason_ID int
Specialty_ID int
SubSpecialty_ID int
ResearchInterest_ID int
OtherInterest_ID int
HitListIndicator tinyint
Mobile nvarchar
PagerNumber nvarchar
Email nvarchar
Email2 nvarchar
EmailPersonalIndicator int
PreCallObjective_ID int
PreCallNote nvarchar
OptionalField1 nvarchar
OptionalField2 nvarchar
OptionalField3 nvarchar
OptionalField4 nvarchar
OptionalField5 nvarchar
OptionalField6 nvarchar
OptionalField7 nvarchar
OptionalField8 nvarchar
OptionalField9 nvarchar
OptionalField10 nvarchar
Country_ID int
IsActive bit
AU_Conv_HCP_ID int
OtherInterest nvarchar
MailingAddress_ID int
Classification_ID int
LastCallDate datetime
LastCallNote nvarchar
Session_ID int
No_Mail tinyint
NamePreferred nvarchar
TrialInterestIndicator int
DuplicateHCP_ID int
Notes nvarchar

Thanks,
Gangadhara MS
SQL Developer and DBA

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-21 : 07:26:16

[code]CREATE TRIGGER [trgHCPUpdate] ON HCP
FOR UPDATE
AS

SET NOCOUNT ON

UPDATE a SET
a.DateModified = GETDATE()
FROM HCP a
INNER JOIN inserted b
ON a.ID = b.ID[/code]

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2010-12-22 : 04:51:52

CREATE TRIGGER [hcpUpdate] ON HCP
FOR UPDATE
AS

SET NOCOUNT ON

UPDATE a SET a.DateModified = GETDATE()
FROM HCP a INNER JOIN inserted b ON a.ID = b.ID

Mageshkumar.M MCA

Sachin.Nand

2937 Posts

Posted - 2010-12-22 : 04:57:21

quote:
Originally posted by MageshkumarM

CREATE TRIGGER [hcpUpdate] ON HCP
FOR UPDATE
AS

SET NOCOUNT ON

UPDATE a SET a.DateModified = GETDATE()
FROM HCP a INNER JOIN inserted b ON a.ID = b.ID

Mageshkumar.M MCA

How is your solution different than what Lumbago suggested except for the trigger name ?

PBUH

Kristen
Test

22859 Posts

Posted - 2010-12-22 : 05:42:18

quote:
Originally posted by Sachin.Nand

How is your solution different than what Lumbago suggested except for the trigger name ?

The formatting has been de-normalised

Sachin.Nand

2937 Posts

Posted - 2010-12-22 : 05:48:18

quote:
Originally posted by Kristen

quote:
Originally posted by Sachin.Nand

How is your solution different than what Lumbago suggested except for the trigger name ?

The formatting has been de-normalised

Damn how could I miss that ? Seems its time for a new pair of glasses for me...

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources