EzDevInfo.com

Are database triggers evil? [closed]

Are database triggers a bad idea?

In my experience they are evil, because they can result in surprising side effects, and are difficult to debug (especially when one trigger fires another). Often developers do not even think of looking if there is a trigger.

On the other hand, it seems like if you have logic that must occur evertime a new FOO is created in the database then the most foolproof place to put it is an insert trigger on the FOO table.

The only time we're using triggers is for really simple things like setting the ModifiedDate.

In WPF, does the order of Triggers matter?

I have the following xaml:

<DockPanel>
    <DockPanel.Resources>
        <Style TargetType="Button">
            <Style.Triggers>
                <Trigger Property="IsMouseOver" Value="True">
                    <Setter Property="Foreground" Value="Yellow"></Setter>
                </Trigger>
                <Trigger Property="IsPressed" Value="True">
                    <Setter Property="Foreground" Value="Green"></Setter>
                </Trigger>
            </Style.Triggers>
        </Style>
    </DockPanel.Resources>
    <Button Content="Cut" Height="30" Width="75"/>
</DockPanel>

What happens is that when I mouse over the button, the foreground changes to yellow and then when I press the button, the foreground changes to green.

Now, if I reverse the order of my triggers in the XAML, the foreground changes to yellow when I mouse over it, but when I press the button, the foreground does NOT change to green.

What is the explanation for this? Is one trigger overriding the other?

How have you successfully implemented MessageBox.Show() functionality in MVVM?

I've got a WPF application which calls MessageBox.Show() way back in the ViewModel (to check if the user really wants to delete). This actually works, but goes against the grain of MVVM since the ViewModel should not explicitly determine what happens on the View.

So now I am thinking how can I best implement the MessageBox.Show() functionality in my MVVM application, options:

1. I could have a message with the text "Are you sure...?" along with two buttons Yes and No all in a Border in my XAML, and create a trigger on the template so that it is collapsed/visible based on a ViewModelProperty called AreYourSureDialogueBoxIsVisible, and then when I need this dialogue box, assign AreYourSureDialogueBoxIsVisible to "true", and also handle the two buttons via DelegateCommand back in my ViewModel.

2. I could also somehow try to handle this with triggers in XAML so that the Delete button actually just makes some Border element appear with the message and buttons in it, and the Yes button did the actually deleting.

Both solutions seem to be too complex for what used to be a couple lines of code with MessageBox.Show().

In what ways have you successfully implemented Dialogue Boxes in your MVVM applications?

SQL update trigger only when column is modified

By looking at other examples I've come up with the following but it doesn't seem to work as I would like: I want it to only update the modified information if the QtyToRepair value has been updated... but it doesn't do that.

If I comment out the where then the modified information is updated in every case. As I said other examples led me to be optimistic. Any clues appreciated. Thanks.

Walter

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]
   ON [dbo].[SCHEDULE]
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    UPDATE SCHEDULE SET modified = GETDATE()
        , ModifiedUser = SUSER_NAME()
        , ModifiedHost = HOST_NAME()
    FROM SCHEDULE S
    INNER JOIN Inserted I on S.OrderNo = I.OrderNo and S.PartNumber = I.PartNumber
    WHERE S.QtyToRepair <> I.QtyToRepair
END

MySQL Trigger after update only if row has changed

Is there any possibility to use an "after update" trigger only in the case the data has been REALLY changed. I know of "NEW and OLD". But when using them I'm only able to compare columns. For example "NEW.count <> OLD.count".

But I want something like: run trigger if "NEW <> OLD"

An Example:

create table foo (a INT, b INT);
create table bar (a INT, b INT);

INSERT INTO foo VALUES(1,1);
INSERT INTO foo VALUES(2,2);
INSERT INTO foo VALUES(3,3);

CREATE TRIGGER ins_sum
    AFTER UPDATE ON foo
    FOR EACH ROW
    INSERT INTO bar VALUES(NEW.a, NEW.b);

UPDATE foo SET b = 3 WHERE a=3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0


select * from bar;
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
+------+------+

The point is, there was an update, but nothing has changed. But the trigger ran anyway. IMHO there should be a way it doesn't.

I know that I could have used

IF NOW.b <> OLD.b

for this example.

BUT imagine a large table with changing columns. You have to compare every column and if the database changes you have to adjust the trigger. AND it doesn't "feel" good to compare every column of the row hardcoded :)

Addition

As you can see on the line

Rows matched: 1 Changed: 0 Warnings: 0

MySQL knows that the line didn't change. But it doesn't share this knowledge with the trigger. A trigger like "AFTER REAL UPDATE" or something like this would be cool.

jQuery.trigger('click') not working

It seems that I just can not understand the jQuery trigger('click') function correctly.

Can someone please tell me why this simple code doesn't work and how to fix?

HTML:

<a id="bar" rel='nofollow' href="http://stackoverflow.com" target="_blank">Don't click me!</a>
<span id="foo">Click me!</span>

jQuery:

jQuery('#foo').on('click', function(){
    jQuery('#bar').trigger('click');
});

Demo: FIDDLE

What I want is to be able to click on #foo (which is working well) and simulating a click on #bar (which is completly ignored without any error messages). Tried also with the jQuery(document).ready(function(){...}) but without success.

SQL Server "AFTER INSERT" trigger doesn't see the just-inserted row

Consider this trigger:

ALTER TRIGGER myTrigger 
   ON someTable 
   AFTER INSERT
AS BEGIN
  DELETE FROM someTable
         WHERE ISNUMERIC(someField) = 1
END

I've got a table, someTable, and I'm trying to prevent people from inserting bad records. For the purpose of this question, a bad record has a field "someField" that is all numeric.

Of course, the right way to do this is NOT with a trigger, but I don't control the source code... just the SQL database. So I can't really prevent the insertion of the bad row, but I can delete it right away, which is good enough for my needs.

The trigger works, with one problem... when it fires, it never seems to delete the just-inserted bad record... it deletes any OLD bad records, but it doesn't delete the just-inserted bad record. So there's often one bad record floating around that isn't deleted until somebody else comes along and does another INSERT.

Is this a problem in my understanding of triggers? Are newly-inserted rows not yet committed while the trigger is running?

How do you list all triggers in a MySQL database?

What is the command to list all triggers in a MySQL database?

What is the most portable way to check whether a trigger exists in SQL Server?

I'm looking for the most portable method to check for existence of a trigger in MS SQL Server. It needs to work on at least SQL Server 2000, 2005 and preferably 2008.

The information does not appear to be in INFORMATION_SCHEMA, but if it is in there somewhere, I would prefer to use it from there.

I do know of this method:

if exists (
    select * from dbo.sysobjects 
    where name = 'MyTrigger' 
    and OBJECTPROPERTY(id, 'IsTrigger') = 1
) 
begin

end

But I'm not sure whether it works on all SQL Server versions.

difference between a FOR and AFTER trigger

what's the difference between a FOR and AFTER trigger.

WPF Trigger for IsSelected in a DataTemplate for ListBox items

I have a listbox, and I have the following ItemTemplate for it:

<DataTemplate x:Key="ScenarioItemTemplate">
    <Border Margin="5,0,5,0"
            Background="#FF3C3B3B"
            BorderBrush="#FF797878"
            BorderThickness="2"
            CornerRadius="5">
        <DockPanel>
            <DockPanel DockPanel.Dock="Top"
                       Margin="0,2,0,0">
                <Button HorizontalAlignment="Left"
                        DockPanel.Dock="Left"
                        FontWeight="Heavy"
                        Foreground="White" />
                <Label Content="{Binding Path=Name}"
                       DockPanel.Dock="Left"
                       FontWeight="Heavy"
                       Foreground="white" />
                <Label HorizontalAlignment="Right"
                       Background="#FF3C3B3B"
                       Content="X"
                       DockPanel.Dock="Left"
                       FontWeight="Heavy"
                       Foreground="White" />
            </DockPanel>
            <ContentControl Name="designerContent"
                            Visibility="Collapsed"
                            MinHeight="100"
                            Margin="2,0,2,2"
                            Content="{Binding Path=DesignerInstance}"
                            Background="#FF999898">
            </ContentControl>
        </DockPanel>
    </Border>
</DataTemplate>

As you can see the ContentControl has Visibility set to collapsed.

I need to define a trigger that causes the Visibility to be set to "Visible"

when the ListItem is selected, but I can't figure it out.

Any ideas?

UPDATE: Of course I could simply duplicate the DataTemplate and add triggers to the ListBox in question to use either one or the other, but I want to prevent duplicating this code.

Setting a property with an EventTrigger

I want to be able to set a property with an EventTrigger, there's a number of problems with this.

1) EventTriggers only support Actions, so I must use a storyBoard to set my properties.

2) Once I use a storyboard, I have two options:

• Stop: Once the animation has stopped the value reverts back to before the animation started
• HoldEnd: This locks the property, so that neither code, nor user interaction can change the property that the animation is holding.

In the below example, I want to set the IsChecked property to False when the button is clicked and I want the user to be able to change the IsChecked and/or I want to be able to change the property in code.

Example:

<EventTrigger
    SourceName="myButton"
    RoutedEvent="Button.Click">
    <EventTrigger.Actions>
        <BeginStoryboard>
            <Storyboard>
                <BooleanAnimationUsingKeyFrames
                    Storyboard.TargetName="myCheckBox"
                    Storyboard.TargetProperty="IsChecked"
                    FillBehavior="Stop">
                    <DiscreteBooleanKeyFrame
                        KeyTime="00:00:00"
                        Value="False" />
                </BooleanAnimationUsingKeyFrames>
            </Storyboard>
        </BeginStoryboard>
    </EventTrigger.Actions>
</EventTrigger>

I realize that I can use the "Completed" event after the storyboard completes to set the value to False. However, in this instance I want to contain the logic within the XAML, as this logic will be used on a custom control and is only specific to the UI.

Trigger change() event when setting 's value with val() function

What is the easiest and best way to trigger change event when setting the value of select element.

I've expected that executing the following code

$('select#some').val(10);

or

$('select#some').attr('value',  10);

would result in triggering the change event, which i think is pretty logic thing to be done. Right?

Well, it isn't the case. You need to triger change() event by doing this

$('select#some').val(10).change();

or

$('select#some').val(10).trigger('change');

but i'm looking for some solution which would trigger change event as soon as select's value is changed by some javascript code.

Source: (StackOverflow)

Need to list all triggers in SQL Server database with table name and table's schema

I need to list all triggers in SQL Server database with table name and table's schema.

I'm almost there with this:

SELECT trigger_name = name, trigger_owner = USER_NAME(uid),table_schema = , table_name = OBJECT_NAME(parent_obj),
  isupdate = OBJECTPROPERTY( id, 'ExecIsUpdateTrigger'), isdelete = OBJECTPROPERTY( id, 'ExecIsDeleteTrigger'),
  isinsert = OBJECTPROPERTY( id, 'ExecIsInsertTrigger'), isafter = OBJECTPROPERTY( id, 'ExecIsAfterTrigger'),
  isinsteadof = OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger'),
  [disabled] = OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') 
FROM sysobjects INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
WHERE type = 'TR'

I just need to get the table's schema also.

Source: (StackOverflow)

MySQL Fire Trigger for both Insert and Update

Is it possible to fire a mysql trigger for both the insert and update events of a table?

I know I can do the following

CREATE TRIGGER my_trigger
    AFTER INSERT ON `table`
    FOR EACH ROW
BEGIN
.....
END //

CREATE TRIGGER my_trigger
    AFTER UPDATE ON `table`
    FOR EACH ROW
BEGIN
.....
END //

But how can I do

CREATE TRIGGER my_trigger
    AFTER INSERT ON `table` AND
    AFTER UPDATE ON `table`
    FOR EACH ROW
BEGIN
.....

Is it possible, or do I have to use 2 triggers? The code is the same for both and I don't want to repeat it.

Source: (StackOverflow)