Solved

Is this correct?

Posted on 2004-10-27
601 Views
Last Modified: 2008-03-03
Hi, This is difficult for me since I know very little about triggers, tables and Databases in general. But... as sometimes happens during my job I've been assigned something out of my league. Now... I've been reading and trying to "reuse" examples to do what I'm trying to do.
So, let me first explain what I'm trying to do. We have this application that runs on Solaris and uses Syabase. The user ID's are defined on a Sybase table (adb.tssuser) and on the Sun OS. I have this software that can automatically create records on both, but just that. So, I can add a user on Solaris and create a home directory. Then I can insert a record on a sybase table. What this software can't do is execute scripts or do file system operations. So, what I have in mind is that after succesfull creation on the OS and on this intermediate table a trigger would trigger and run a script that would do the rest. The script has to be called with the userid and the cat fields added. Keep in mind that I'll be writing to a intermediate table and then, with a trigger, updating the definitve table (adb.tssuser). This is what I've come up to:


USE db
SET CHAINED OFF

CREATE TABLE emp
(
      empno            NUMERIC(8)      IDENTITY,
      usrid            NVARCHAR(64) NULL,
      cat            CHAR(64) NULL,
      CONSTRAINT pk_empno PRIMARY KEY (empno),
)
GO


/* holds last generated identity value */
CREATE TABLE empno
(
      empno      NUMERIC(8)      NOT NULL,
      CONSTRAINT unq_empno UNIQUE (empno)
)
INSERT INTO empno VALUES(-1)
GO

CREATE PROCEDURE sp_empno
      @io_empno      NUMERIC OUTPUT,
      @i_userid       VARCHAR
AS

DECLARE @key NUMERIC(8)

IF (@io_empno IS NULL)
BEGIN

      DECLARE rs CURSOR FOR SELECT empno FROM empno
      OPEN rs
      FETCH rs INTO @key
      IF(@key = -1)
            SELECT @io_empno = NULL
      ELSE
            SELECT @io_empno = @key
      CLOSE rs
      DEALLOCATE CURSOR rs
END
GO
sp_procxmode sp_empno, "anymode"
GO


/****************************************************************************/
/* Create event log table.                                                  */
/****************************************************************************/

CREATE TABLE eventlog
(
      record_id      NUMERIC(38)      IDENTITY
                              NOT NULL,
      table_key      VARCHAR(64)      NOT NULL,
      status      CHAR(1)      DEFAULT 'N',
      event_type      NUMERIC(1)      NOT NULL
                              CHECK (event_type IN (1, 2, 3, 4, 5, 6, 7, 8)),
      event_time      DATETIME      DEFAULT getDate()
                              NOT NULL,
      perpetrator      VARCHAR(64)      DEFAULT USER
                              NULL,
      table_name      VARCHAR(64)      NOT NULL,
      column_name      VARCHAR(64) NULL,
      old_value      NVARCHAR(64) NULL,
      new_value      NVARCHAR(64) NULL
)

CREATE UNIQUE NONCLUSTERED INDEX i_eventlog_1 ON eventlog(table_key, record_id, status)
CREATE UNIQUE NONCLUSTERED INDEX i_eventlog_2 ON eventlog(record_id, table_key)
GO


CREATE TRIGGER t_emp_insert
      ON emp
      FOR INSERT
AS

DECLARE
      @opcode            SMALLINT,
      @date_style       SMALLINT,
      @table_name       VARCHAR(64),
      @key                   VARCHAR(64),
      @new_empno            VARCHAR(64)

      UPDATE empno SET empno = (SELECT new.empno FROM inserted new)

      SELECT @opcode       = 5      /* Insert (Query-Back) */
      SELECT @date_style = 109  /* mon dd yyy hh:mm:sss AM (or PM) */
      SELECT @table_name = 'emp'

      SELECT @new_empno = LTRIM(STR(new.empno)) from inserted new
      SELECT @key = "empno=" + @new_empno

      INSERT INTO dbo.eventlog(event_type, table_name, table_key)
            VALUES(@opcode, @table_name, @key)
GO

CREATE TRIGGER t_emp_delete
    ON emp
    FOR DELETE

AS

      INSERT INTO dbo.eventlog(event_type, table_name, table_key)
            (SELECT 4, 'emp', 'empno=' + LTRIM(STR(old.empno)) from deleted old)
GO
CREATE TRIGGER t_emp_scp_add
      ON emp
      FOR INSERT
AS

DECLARE
      @opcode            SMALLINT,
      @date_style       SMALLINT,
      @table_name       VARCHAR(64),
      @key                   VARCHAR(64),
      @new_empno            VARCHAR(64)

      SELECT @opcode       = 5      /* Insert (Query-Back) */
      SELECT @date_style = 109  /* mon dd yyy hh:mm:sss AM (or PM) */
      SELECT @table_name = 'emp'

      SELECT @new_empid = STR(new.userid) from inserted new
      SELECT @categ = STR(new.cat) from inserted new
        select @script = "/home/nvl/oss_novell.sh " + @new_empid + " " + @categ
        execute xp_cmdshell @script
GO


Thanks for your help!!!
Nico
0
Question by:nickoarg
    7 Comments
     
    LVL 3

    Author Comment

    by:nickoarg
    Note that there might be things and commands that shouldn't and things that do nothing... this is due to my lack of DB knowledge...
    0
     
    LVL 6

    Expert Comment

    by:ChrisKing
    your triggers are written as if you will only ever be processing one row at a time (common mistake)

    you should also know that xp_cmdshell can not be rolled back, so :

    begin tran
    insert into emp ...
    rollback

    will cal the /home/nvl/oss_novell.sh script but you won't have the row in te database
    0
     
    LVL 3

    Author Comment

    by:nickoarg
    The idea is that only one row at a time can be inserted, never more than 1 in one operation.

    ..."but you won't have the row in te database"...
    Why? Does the trigger acts before the row is inserted in the table "emp"?

    "begin tran
    insert into emp ...
    rollback"
    please, note that I really don't know about databases... I've base all that on examples and existing code... where do I put that? how should it look to be correct?
    thanks
    0
     
    LVL 6

    Expert Comment

    by:ChrisKing
    > The idea is that only one row at a time can be inserted, never more than 1 in one operation.
    that idea might be OK for this week, but next month or next year or later, someone will insert more than one row and the triggers will not function as expected

    to protect yourself (asssuming you don want to rewrite them), put the following code into the top of the triggers.

    IF @@rowcount > 1
      BEGIN
        ROLLBACK TRIGGER WITH RAISERROR <put your error details here>
        RETURN
      END

    > Why? Does the trigger acts before the row is inserted in the table "emp"?
    the trigger does not  act before the row is inserted, the rollback will force everything that was just inserted/updated/deleted to be undone, but you can not undo a execution of a unix script. This is a known limitation of the xp commands.
    0
     
    LVL 3

    Author Comment

    by:nickoarg
    "someone will insert more than one row and the triggers will not function as expected"
    no..., the updates come from a software that updates only one row at a time, but I undestand what you say.

    I still don't understand the rollback...
    step 1, a new row is inserted in the table emp
    step 2, the trigger t_emp_scp_add fires away and the xp_cmdshell would run my script

    why do I want to remove new the row from table emp? (assuming that that is what rollback does)
    0
     
    LVL 6

    Accepted Solution

    by:
    in simple terms ROLLBACK means don't keep the changes I just made, whereas COMMIT means keep the changes
    To better understand it, read up on "transaction control"

    try the following script:

    CREATE TABLE #test (num int NOT NULL)

    BEGIN TRAN
    INSERT INTO #test SELECT 1
    INSERT INTO #test SELECT 2
    INSERT INTO #test SELECT 3
    COMMIT
    SELECT * FROM #test                     -- will show 1, 2 and 3

    BEGIN TRAN
    INSERT INTO #test SELECT 4
    INSERT INTO #test SELECT 5
    SELECT * FROM #test                     -- will show 1, 2, 3, 4 and 5
    ROLLBACK
    SELECT * FROM #test                     -- will show 1, 2 and 3

    BEGIN TRAN
    UPDATE #test SET num = num + 100
    SELECT * FROM #test                     -- will show 101, 102 and 103
    ROLLBACK
    SELECT * FROM #test                     -- will show 1, 2 and 3

    BEGIN TRAN
    DELETE FROM #test
    SELECT * FROM #test                     -- will show no rows
    ROLLBACK
    SELECT * FROM #test                     -- will show 1, 2 and 3

    DROP TABLE #test
    go

    ===============
    actual script output
    ===============
    (1 row affected)
    (1 row affected)
    (1 row affected)
     num
     -----------
               1
               2
               3

    (3 rows affected)
    (1 row affected)
    (1 row affected)
     num
     -----------
               1
               2
               3
               4
               5

    (5 rows affected)
     num
     -----------
               1
               2
               3

    (3 rows affected)
    (3 rows affected)
     num
     -----------
             101
             102
             103

    (3 rows affected)
     num
     -----------
               1
               2
               3

    (3 rows affected)
    (3 rows affected)
     num
     -----------

    (0 rows affected)
     num
     -----------
               1
               2
               3

    (3 rows affected)
    0
     
    LVL 6

    Expert Comment

    by:ChrisKing
    I am suprised:
    - this question was not even pinged
    - and no answer is actually marked as accepted
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Anonabox PRO Tor & VPN Router

    PRO is the most advanced way to fortify your privacy and online anonymity by layering the Tor network with VPN services. Use both together or separately, and without needing to download software onto your devices.

    Finding a job can be stressful - searches, resume tweaks, and networking events can be super boring. Luckily we're here to help you land your dream job!
    Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…

    846 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now