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

Trigger

Author

Topic

khasim76
Starting Member

35 Posts

Posted - 2008-09-05 : 16:37:46

Create TRIGGER INS_ADD_CORR
On ISSUER_ADDRESS_CORRECTIO FOR INSERT
AS
Begin
@NEW.date_created() = getdate()
@NEW.date_modified() = getdate()

End

Whats the porblem with this plz let me know Asap

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-05 : 16:44:25

The syntax is incorrect. What are you trying to do? Is this even a SQL Server question?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khasim76
Starting Member

35 Posts

Posted - 2008-09-05 : 17:13:22

i am new to T-sql, what i m trying to create a trigger wwhich is in oracle and i am trying to convert into T-sQL . I need to know equivalent in t-sql for functions like old, new which are in oracle.

Regards

Khasim Shaik

As follows

create or replace TRIGGER "BILLINGDV".UPD_CE_MONTH_FACT
BEFORE UPDATE OF
SHARES,
PRICE,
MARKET_CAP
on CE_MONTH_FACT

for each row
WHEN (old.market_cap <> new.market_cap)

declare numrows INTEGER;
begin
:NEW.date_modified := SYSDATE;
end;

quote:
Originally posted by khasim76

Create TRIGGER INS_ADD_CORR
On ISSUER_ADDRESS_CORRECTIO FOR INSERT
AS
Begin
@NEW.date_created() = getdate()
@NEW.date_modified() = getdate()

End

Whats the porblem with this plz let me know Asap

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-06 : 23:55:26

something like this:-

create or replace TRIGGER "BILLINGDV".UPD_CE_MONTH_FACT
on CE_MONTH_FACT
INSTEAD OF UPDATE 
AS

UPDATE c
SET c.otherfields=i.otherfields....,
c.date_modified=CASE WHEN i.market_cap <> d.market_cap THEN GETDATE() END
FROM CE_MONTH_FACT c
INNER JOIN INSERTED i
ON i.PK=c.PK
INNER JOIN DELETED d
ON d.PK=i.PK

khasim76
Starting Member

35 Posts

Posted - 2008-09-07 : 00:10:18

thanks but could you explain me this i.i know i am being stupid but please understand that i am very new to concept oof triggers in both database be it oracle or SQL server 2005. and also could you explain
inner join inserted i

is i a resultant table after insert and delete. hope i am correct on this matter.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-07 : 00:17:05

quote:
Originally posted by khasim76

thanks but could you explain me this i.i know i am being stupid but please understand that i am very new to concept oof triggers in both database be it oracle or SQL server 2005. and also could you explain
inner join inserted i

is i a resultant table after insert and delete. hope i am correct on this matter.

just like you've new and old in oracle, inserted and deleted are two internal temporary tables available in sql server. when you create a trigger on table for update,insert or delete action. whenever such an action hppens, it causes trigger to fire. Internally sql server does update by means of these two tables. the old values will be available in DELETED and new values in INSERTED. The code i've given checks contents from both tables and sets date_modified value to current system date (given by GETDATE() function) when old market_cap value not equal to new market_cap value.

khasim76
Starting Member

35 Posts

Posted - 2008-09-07 : 00:21:33

now i got a better idea. Thank you very much

quote:
Originally posted by visakh16

quote:
Originally posted by khasim76

thanks but could you explain me this i.i know i am being stupid but please understand that i am very new to concept oof triggers in both database be it oracle or SQL server 2005. and also could you explain
inner join inserted i

is i a resultant table after insert and delete. hope i am correct on this matter.

just like you've new and old in oracle, inserted and deleted are two internal temporary tables available in sql server. when you create a trigger on table for update,insert or delete action. whenever such an action hppens, it causes trigger to fire. Internally sql server does update by means of these two tables. the old values will be available in DELETED and new values in INSERTED. The code i've given checks contents from both tables and sets date_modified value to current system date (given by GETDATE() function) when old market_cap value not equal to new market_cap value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-07 : 00:36:17

quote:
Originally posted by khasim76

now i got a better idea. Thank you very much

You're welcome

khasim76
Starting Member

35 Posts

Posted - 2008-09-07 : 00:41:16

Sorry for this simple doubt.

so we are setting rest of the columns of month_fact table as it is to the insert table i

when you say Set c.otherfields = i.otherfields.

so i have to list out all the remaining columns
like c. name = i.name
c. country = i. country
Plz let me know

quote:
Originally posted by khasim76

now i got a better idea. Thank you very much

quote:
Originally posted by visakh16

quote:
Originally posted by khasim76

thanks but could you explain me this i.i know i am being stupid but please understand that i am very new to concept oof triggers in both database be it oracle or SQL server 2005. and also could you explain
inner join inserted i

is i a resultant table after insert and delete. hope i am correct on this matter.

just like you've new and old in oracle, inserted and deleted are two internal temporary tables available in sql server. when you create a trigger on table for update,insert or delete action. whenever such an action hppens, it causes trigger to fire. Internally sql server does update by means of these two tables. the old values will be available in DELETED and new values in INSERTED. The code i've given checks contents from both tables and sets date_modified value to current system date (given by GETDATE() function) when old market_cap value not equal to new market_cap value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-07 : 00:43:44

quote:
Originally posted by khasim76

Sorry for this simple doubt.

so we are setting rest of the columns of month_fact table as it is to the insert table i

when you say Set c.otherfields = i.otherfields.

so i have to list out all the remaining columns
like c. name = i.name
c. country = i. country
Plz let me know

quote:
Originally posted by khasim76

now i got a better idea. Thank you very much

quote:
Originally posted by visakh16

quote:
Originally posted by khasim76

thanks but could you explain me this i.i know i am being stupid but please understand that i am very new to concept oof triggers in both database be it oracle or SQL server 2005. and also could you explain
inner join inserted i

is i a resultant table after insert and delete. hope i am correct on this matter.

just like you've new and old in oracle, inserted and deleted are two internal temporary tables available in sql server. when you create a trigger on table for update,insert or delete action. whenever such an action hppens, it causes trigger to fire. Internally sql server does update by means of these two tables. the old values will be available in DELETED and new values in INSERTED. The code i've given checks contents from both tables and sets date_modified value to current system date (given by GETDATE() function) when old market_cap value not equal to new market_cap value.

yeah. you need to give all other fields. i just gave otherfields as i dont know your field names.

khasim76
Starting Member

35 Posts

Posted - 2008-09-07 : 12:06:47

create TRIGGER UPD_CE_MONTH_FACT
BEFORE UPDATE OF
SHARES,
PRICE,
MARKET_CAP
on CE_MONTH_FACT

please let me know how do i write in TSQL this much part of trigger.

quote:
Originally posted by khasim76

Create TRIGGER INS_ADD_CORR
On ISSUER_ADDRESS_CORRECTIO FOR INSERT
AS
Begin
@NEW.date_created() = getdate()
@NEW.date_modified() = getdate()

End

Whats the porblem with this plz let me know Asap

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-07 : 15:10:58

quote:
Originally posted by khasim76

create TRIGGER UPD_CE_MONTH_FACT
BEFORE UPDATE OF
SHARES,
PRICE,
MARKET_CAP
on CE_MONTH_FACT

please let me know how do i write in TSQL this much part of trigger.

quote:
Originally posted by khasim76

Create TRIGGER INS_ADD_CORR
On ISSUER_ADDRESS_CORRECTIO FOR INSERT
AS
Begin
@NEW.date_created() = getdate()
@NEW.date_modified() = getdate()

End

Whats the porblem with this plz let me know Asap

didnt i give you the code earlier

create  TRIGGER UPD_CE_MONTH_FACT on CE_MONTH_FACT
  INSTEAD OF  UPDATE 
AS
        IF UPDATE(SHARES) OR 
          UPDATE(PRICE) OR
         UPDATE(MARKET_CAP)
....

khasim76
Starting Member

35 Posts

Posted - 2008-09-07 : 17:30:18

THAT DIDN'T HAD COLUMN SELECTION IT HAD all table selection.thank u very much with his help i will go way long from here

khasim76
Starting Member

35 Posts

Posted - 2008-09-08 : 10:02:01

if you are there again what if i have 4 primary keys how the syntax would be plz help.
create TRIGGER UPD_CE_MONTH_FACT
on CE_MONTH_FACT
INSTEAD OF UPDATE
AS

UPDATE c
DECLARE @company_key int
@company_version int
@year_id int
@month_id int
@shares INT
@price INT
@Net_Asset_Value VARCHAR(27)
@Nav_Date DATETIME
@Market_CAP NUMERIC(17,2)
@DATE_CREATED DATETIME
@DATE_MODIFIED DATETIME
@CUSIP VARCHAR(15)
@ERROR_FOUND VARCHAR(1)
@EXCH VARCHAR(8)
@ROWID UNIQUEIDENTIFIER

SET
c.company_key = i.company_key,
c.company_version = i.company_version,
c.year_id = i. year_id,
c.month_id= i.month_id,
c.shares = i.shares,
c.price = i.price,
c.Net_Asset_Value = i.Net_Asset_Value,
c.Nav_Date = i.Nav_Date,
c.Market_CAP = i.Market_CAP,
c.DATE_CREATED = i.DATE_CREATED,
c.DATE_MODIFIED = i.DATE_MODIFIED,
c.CUSIP = i.CUSIP,
c.ERROR_FOUND = i.ERROR_FOUND,
c.EXCH =i.EXCH,
c.ROWID =i.ROWID,
c.date_modified=CASE WHEN i.market_cap <> d.market_cap THEN GETDATE() END
FROM CE_MONTH_FACT c
INNER JOIN INSERTED i
ON i.company_key = c.company_key
i.company_version = c.company_versio
i.year_id = c.year_id
i.month_id =c.month_id
INNER JOIN DELETED d
ON
d.company_key = i.company_key
d.company_version = i.company_versio
d.year_id = i.year_id
d.month_id =i.month_id

khasim76
Starting Member

35 Posts

Posted - 2008-09-08 : 10:03:23

Also these are the errors

Msg 156, Level 15, State 1, Procedure UPD_CE_MONTH_FACT, Line 7
Incorrect syntax near the keyword 'DECLARE'.
Msg 102, Level 15, State 1, Procedure UPD_CE_MONTH_FACT, Line 8
Incorrect syntax near '@company_version'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources