March 3, 2011 at 7:19 am
0 down vote favorite
I am working with a database name xyz , table name a ,b, c, d. my question is if in table a name field , table b email field, table c phone number , change/update I want to make table d field upload to 1.
These tables has many fields, but I only want to change table d field when given three field change in three different table.
March 3, 2011 at 7:37 am
naveedanjum1 (3/3/2011)
0 down vote favoriteI am working with a database name xyz , table name a ,b, c, d. my question is if in table a name field , table b email field, table c phone number , change/update I want to make table d field upload to 1.
These tables has many fields, but I only want to change table d field when given three field change in three different table.
you really need to provide more details, specifically the actual table designs (CREATE TABLE a...)
in general, you want an UPDATE FROM statement;lookup the update from syntax in Books online.
here is a general example, where I'm updating a table based on another.
in your specific case, instead of just one table being in the FROM statement, it would(or could) be three or more.
UPDATE WHATEVER
SET WHATEVER.email = OTHERTABLE.EMAIL
FROM OTHERTABLE
WHERE WHATEVER.WHATEVERID = OTHERTABLE.WHATEVERID
Lowell
March 3, 2011 at 12:11 pm
CREATE TRIGGER dbo.YourTrigger
ON a
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF NOT UPDATE(name)
RETURN
UPDATE d
set upload = 1
FROM d
END
This is my code, but it's not working the way i want it. in this case its updating my upload field when every any record change .
i want upload field in table d to be change only when (name field) change in Table a.
March 3, 2011 at 12:27 pm
the UPDATE command is really misleading...it only identifies if the column was specifically listed int eh column names of the update. It does not tell you if it changed or not;
pseudo code kills you...no details means an incomplete example you have to adapt... tables with single letter names are poor examples.
here's a better example of your trigger:
CREATE TRIGGER dbo.YourTrigger
ON [a]
FOR UPDATE
AS
BEGIN
--if any rows exist where the name changed.
IF EXISTS(SELECT 1
FROM INSERTED
INNER JOIN DELETED
ON INSERTED.PK = DELETED.PK
WHERE INSERTED.name <> DELETED.name
)
--update what? a single flag in a table with a single row? makes no sense...assuming there must be a relationship between the tables [a] and [d]
UPDATE [d]
SET newname = INSERTED.name,
upload = 1
FROM INSERTED
WHERE [d].PK = INSERTED.PK
END --TRIGGER
Lowell
March 3, 2011 at 3:11 pm
Thanks, i will try this
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply