how can creat 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

SQL Server 2005 Forums

Transact-SQL (2005)

how can creat trigger

Author

Topic

mohamedhsn20
Starting Member

3 Posts

Posted - 2008-07-24 : 12:07:13

i have sqlserver2005 database for store
transaction table 4 fields (date,item_code,qnty,user_name)
i want to create trigger when any user update qunty in field [qnty]
the trigger write the [old qnty] and the [new qnty] in new table and the user who did that and date
thanks for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 12:50:46

[code]CREATE TRIGGER YourTrigger ON YourTable
AFTER INSERT
AS
IF UPDATE(qnty) AND
(SELECT COUNT(*) FROM INSERTED i JOIN DELETED d
ON d.PK=i.PK AND d.qnty<>i.qnty)>0
INSERT INTO NewTable (PK,newval,oldval,user)
SELECT i.PK,i.qnty,d.qnty,SUSER_SNAME()
FROM INSERTED i
INNER JOIN DELETED d
ON d.PK=i.PK
GO[/code]

PK is key of your transaction table

mohamedhsn20
Starting Member

3 Posts

Posted - 2008-07-25 : 07:34:17

thanks
but what can i do if there is no primary key in my transaction table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 07:44:37

quote:
Originally posted by mohamedhsn20

thanks
but what can i do if there is no primary key in my transaction table

what?? why you dont have a primarykey in your table?so how will you be distinguishing a row in that table?

mohamedhsn20
Starting Member

3 Posts

Posted - 2008-07-25 : 08:27:56

this table contain all transaction for all stores every day
i can send you data for one day to see it
but i do'nt know how ??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 10:25:49

quote:
Originally posted by mohamedhsn20

this table contain all transaction for all stores every day
i can send you data for one day to see it
but i do'nt know how ??

if you dont have a primary key, just use columns that uniquely distinguishes a row in table in place of PK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources