Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: using trigger

  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: using trigger

    Hi All,
    am trying to add 2 seperate colums from seperate tables i.e column1 should be added to column 2 when inserted and i want to use a trigger but i dnt knw the syntax to use

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    que pasta!!!!

    You need to be a little more specific

    Post the DDL of your tables

    What DML action Occurs and what you expect to happen
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If the columns are in the same table then you might want to consider adding a calculated column.

    To clarify what Brett has put above: we need more detail! Provide your table definitions and describe what you want to happen and why!
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2012
    Posts
    2

    trigger

    okay this are the tables

    create table items.itemdetails
    (itemid int identity (1,1) CONSTRAINT pkitemid primary key,
    itemname varchar (20)not null,
    itemdescription varchar (20) not null,
    quantityinhand int constraint chkquantinhan check (quantityinhand >0) ,
    unitprice int constraint chckunitprice check (unitprice > 0),
    reorderquantity int constraint chckreordquant check (reorderquantity > 0),
    reorderlevel int constraint chckreorderlevel check (reorderlevel > 0),
    categoryid int constraint FKcatid foreign key
    (categoryid) references items.productcategory(categoryid)not null,
    supplierid int constraint FKsupid foreign key
    (supplierid) references supplier.supplierdetails(supplierid)not null)



    create table transactions.orderdetails
    (
    purchaseorderid int identity (1,1)constraint pkpurid primary key,
    employeeid int not null,
    orderdate datetime ,
    receivingdate datetime constraint chkdate check (receivingdate = getdate()),
    itemid int not null,
    quantityordered int ,
    quantityreceived int ,
    unitprice int not null,
    shipmethod varchar (45) constraint chkmthd check ( shipmethod in('express','standard'))not null,
    orderstatus varchar(15) constraint chkorder
    check(orderstatus in('intransit','received','cancelled')),
    constraint fkitemid foreign key (itemid)
    references items.itemdetails(itemid) ,
    constraint chkordredate check (orderdate <= getdate()),
    constraint chckquantordered check (quantityordered >0),
    constraint chckqtyrec check ((quantityreceived < quantityordered) or (quantityreceived >0)),
    constraint fkempid foreign key (employeeid) references humanresources.employee(employeeid)
    )



    quantityreceived should be added to quantityinhand in the items table and when a record is inserted inside quantityreceived,quantityinhand should b updated automatically.............


    thanks guys
    Last edited by esedee; 02-19-12 at 22:25.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Syntax? That is easy. The internet knows everything...

    CREATE TRIGGER (Transact-SQL)

    However, and this might be a bad reflection on the talent I have around me at the moment, but I generally only let senoir developers write triggers. There are just too many easy ways to screw them up or bring your system to crushing screeching halt. But if you follow that link, you should have the loaded gun you have asked for.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Many years ago I think I mentioned here once, that triggers exist (or will exist) in the environment only under these circumstances:

    1. The DBA/Developer is an imposter
    2. The DBA/Developer doesn't know what else modifies a table that he decided to have a trigger for
    3. The DBA/Developer doesn't want to know, because he's looking for another job, preferrably outside the industry
    4. The way data in a table is accessed is outside of the DBA/Developer's control

    So, which one are you?

    #5 - the version of SQL Server is 4.2
    Last edited by rdjabarov; 02-22-12 at 12:30. Reason: Forgot #5
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    4.
    Which is why my philosophy is keep application rules as close to the application as possible, and keep data rules as close to the data as possible. This frequently means triggers are the best option.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The blind dude!!!!

    I ONLY Use Triggers to track data changes

    But is one of the first things I look for when people call me in because "magic" is happening
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmm...#4...This also means that the application is written by people who had very little to no clue how to write applications. But that's me talking
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It is good to see you again rdjabarov!

    I'll extend your option number 5 through SQL 6.5. Before SQL 7.0, triggers were sometimes the sanest choice that the DBA had for some policing of the data.

    I will add a 4.A option of "Because management said so" and a 4.B "Because the vendor requires them" even though those are both special cases of the more general item 4.

    I will also add:

    6. Legislation isn't always expressable using CHECK constraints, meeting legal requirements may require code instead of expressions.
    7. Triggers can enforce constraints across multiple rows (tuples), and as far as I know SQL constraints can't do that yet.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Can you elaborate on #7, Pat?

    ...and #5 - I started with 4.2a on OS/2, but when 6.0 (eval) came out - it was all over for triggers and RI for me. id you have a different experience?
    Last edited by rdjabarov; 03-09-12 at 22:29. Reason: Addition
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Complex cardinality can be enforced easily using triggers, but I don't know of any way to do it using constraints. An example would be in healthcare where there can be a practical infinity of providers for a patient, but only three can be labeled as "Primary" at one time.

    Another example would be in the legal peer-review system, where the reviewer can't be in the same "reporting chain" as the person that wrote the original opinion. The respondant and the reviewer must be peers, but they may not report directly or indirectly to anyone else in the reporting chain except for the senior partner or members of the ethics committee.

    In regard to your point #5, my luck with SQL 6.0 was abominable. It was too fragile for real work and if 6.5 had been even a few months later we would have reverted several major systems back to 4.21b to wait it out.

    SQL 6.5 was a vast improvement. It did have some peculiar RI peformance issues with tables that had too many indexes on the PK column. In those cases, a trigger was recommended by MS-PSS and it was the only solution that we found. We often included the PK column as a "rightmost tie breaker" for indexes that would otherwise not be unique, so we frequently hit this problem for complex tables.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Pardon the intrusion into a nice piece of writing, but did you mean "...only three can be labeled as "Primary" at one time." or only one can be primary at a time?

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, as ridiculous as it sounds the rules state that there can be up to three "Primary" providers at any point in time.

    English and Bureaucratese look very similar, but the underlying rules are completely different!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by Pat Phelan View Post
    No, as ridiculous as it sounds the rules state that there can be up to three "Primary" providers at any point in time.

    English and Bureaucratese look very similar, but the underlying rules are completely different!

    -PatP
    Interesting business rules? How does one go about distinguishing which one is the primary provider? Or am I missing something?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •